What is the result of following SQL Query?

by

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: 

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

More Related Contents:

  1. How to display data in JOIN, and return null value? [closed]
  2. How can I do a FULL OUTER JOIN in MySQL?
  3. INNER JOIN ON vs WHERE clause
  4. SQL JOIN – WHERE clause vs. ON clause
  5. SQL JOIN: is there a difference between USING, ON or WHERE?
  6. Can I use CASE statement in a JOIN condition?
  7. Difference between JOIN and INNER JOIN
  8. Why isn’t SQL ANSI-92 standard better adopted over ANSI-89?
  9. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  10. SQL to LINQ with multiple join, count and left join
  11. Multiple INNER JOIN SQL ACCESS
  12. Oracle “(+)” Operator
  13. JOIN two SELECT statement results
  14. FULL OUTER JOIN with SQLite
  15. Implement paging (skip / take) functionality with this query
  16. Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
  17. Mixing implicit and explicit JOINs
  18. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  19. Record returned from function has columns concatenated
  20. Is a JOIN faster than a WHERE?
  21. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  22. Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL [duplicate]
  23. PostgreSQL JOIN with array type with array elements order, how to implement?
  24. How to exclude rows that don’t join with another table?
  25. SQL join on multiple columns in same tables
  26. Left Join without duplicate rows from left table
  27. Is NATURAL (JOIN) considered harmful in production environment?
  28. SQL select join: is it possible to prefix all columns as ‘prefix.*’?
  29. Removing duplicate rows (based on values from multiple columns) from SQL table
  30. sql query joins multiple tables – too slow (8 tables)

Leave a Comment