What is the result of following SQL Query?

Answers (b) and (d) are totally wrong. The query won’t return any rows from TableA.

If there are any matching rows are found in TableA, those rows get excluded by the condition in the WHERE clause. (Note that any value of columnName that satisfies the equality comparison in the ON clause will be non-NULL, and that value will not satisfy the “IS NULL” condition in the WHERE clause.)

Answer (c) is also wrong. The query will return rows from TableB for which there is no matching row found in TableA.

The least wrong answer is (a) … but it is still wrong in that the choice of words does not accurately convey the definition of the set.

The query is an anti-join pattern.

The query defines a set of rows found in TableB which do not have a matching row in TableA.

Another way of specifying that set is with a NOT EXISTS condition with a correlated subquery:

   FROM tableB b
        ( SELECT 1
            FROM tableA a
           WHERE a.columnName = b.columnName )

Browse More Popular Posts

Leave a Comment