How does 'exists' affect output

Question

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)
        ))

Answer 1

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         ║
        ╚═══════════╝