3

I previously asked a question about how to organize a long search query. I have taken it apart. I am presently attempting to understand the function of some of its parts. Specifically, I am attempting to determine how an exists statement works. Does it return a single row or multiple rows?

exists(select ID
       From Address
       Join PersonAddress on PersonAddress.Address = Address.ID
       Where
       PersonAddress.Person = Person.ID 
       and ((Description LIKE keyword)
         or(Street LIKE KEYWORD)
         or(City LIKE KEYWORD)
         or(State like KEYWORD)
        ))
  • 3
    exists returns a boolean value of true if the subquery returns at least one row, and false if the subquery returns 0 rows. So in your case, this exists() block will equal true if at least one address exists for the given person, which satisfies description/street/... conditions. – zgguy Jun 9 '15 at 20:06
  • 2
    Are you referring to a specific rdbms? – Tom V Aug 22 '17 at 19:28
3

According to the Microsoft Docs page for the EXISTS operator:

EXISTS returns TRUE if a subquery contains any rows.

The PostgreSQL documentation page says this about the exists function:

The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is "true"; if the subquery returns no rows, the result of EXISTS is "false".

Therefore, an EXISTS statement is used as a boolean expression to determine if some subset of rows exists for a given situation.

Take the following example:

CREATE TABLE table1
(
    key_value int
);

INSERT INTO table1 (key_value)
VALUES (0)
    , (1)
    , (2)
    , (3);

CREATE TABLE table2
(
    key_value int
);

INSERT INTO table2 (key_value)
VALUES (0)
    , (2);

So, table1 contains 4 rows, and table2 contains 2 rows.

This query will show results from table1 where the key_value matches any row in table2 that has the same key_value:

SELECT *
FROM table1
WHERE EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.key_value = table1.key_value
    );
╔═══════════╗
║ key_value ║
╠═══════════╣
║ 0         ║
║ 2         ║
╚═══════════╝

This is similar to a simple INNER JOIN in that only rows where key_value matches in both tables are returned, however there are several key differences:

  1. The results only show columns contained in table1, whereas an INNER JOIN query like the one below returns columns from both tables.

    SELECT *
    FROM table1
        INNER JOIN table2 
            ON table1.key_value = table2.key_value;
    
        ╔═══════════╦═══════════╗
        ║ key_value ║ key_value ║
        ╠═══════════╬═══════════╣
        ║ 0         ║ 0         ║
        ║ 2         ║ 2         ║
        ╚═══════════╩═══════════╝
    
  2. If there are multiple rows in table2 that match a single row in table1, only a single row is returned by the EXISTS query, whereas multiple rows (the cartesian product of matching rows in table1 and table2) would be returned by the INNER JOIN query. Let's insert another row into table2 so we have two rows where key_value is 2:

    INSERT INTO table2 (key_value)
    VALUES (2);
    
    SELECT *
    FROM table1
        INNER JOIN table2 
            ON table1.key_value = table2.key_value;
    
     ╔═══════════╦═══════════╗
     ║ key_value ║ key_value ║
     ╠═══════════╬═══════════╣
     ║ 0         ║ 0         ║
     ║ 2         ║ 2         ║
     ║ 2         ║ 2         ║
     ╚═══════════╩═══════════╝

    The EXISTS version of the query still only returns two rows:

    SELECT *
    FROM table1
    WHERE EXISTS (
        SELECT 1
        FROM table2
        WHERE table2.key_value = table1.key_value
        );
    
        ╔═══════════╗
        ║ key_value ║
        ╠═══════════╣
        ║ 0         ║
        ║ 2         ║
        ╚═══════════╝

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.