Join VS Exists

Carlito · October 8, 2020

SQL

EXISTS is used to return a boolean value, JOIN returns a whole other table. EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation.

Use EXISTS when:

  • You don’t need to return data from the related table
  • You want to check existence (use instead of LEFT OUTER JOIN...NULL condition)

JOIN syntax is easier to read and clearer normally as well.

Example

Let’s take the two tables:

  1. Table 1 AS T1 contains only IDs
  2. Table 2 AS T2 could be the EnterpriseAttribute data view.

Objective

Check if the T1 contains IDs based in Germany (or any other country).

Using EXISTS:

SELECT T1.Key
FROM Table1 AS T1
WHERE EXISTS
    (SELECT Column
    FROM ENT._EnterpriseAttribute AS T2
    WHERE
        T2.Key = T1.Key
        AND
        Country = 'Germany')

Result

T1.Key
1234567890
0987654321
1324354657

Using JOIN:

SELECT
    T1.Key
    ,T2.Country
    ,T2.Attribute1
    ,T2.Attribute2
    ,T2.Attribute3
FROM
    Table1 AS T1
    INNER JOIN
    ENT._EnterpriseAttribute AS T2
    ON
    T1.Key = T2.Key
WHERE
    T2.Country = 'Germany'

Result

T1.Key T2.Country T2.Attribute1 T2.Attribute2 T2.Attribute3
1234567890 Germany value value value
0987654321 Germany value value value
1324354657 Germany value value value

Resources:

Twitter, Facebook