Skip to content
Fix Code Error

How to select all records from one table that do not exist in another table?

March 13, 2021 by Code Error
Posted By: Anonymous

table1 (id, name)
table2 (id, name)

Query:

SELECT name   
FROM table2  
-- that are not in table1 already

Solution

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

Answered By: Anonymous

Related Articles

  • SQL query return data from multiple tables
  • SQL find sum of entries by date including previous date
  • SQL JOIN and different types of JOINs
  • error LNK2005: ✘✘✘ already defined in…
  • Form field border-radius is not working only on the last…
  • SQL Server JOIN missing NULL values
  • Using IS NULL or IS NOT NULL on join conditions - Theory…
  • Union of multiple Database queries with same parameters
  • Pandas Merging 101
  • Is there a SQL function I can use to rotate a sequence…

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How can I read inputs as numbers?

Next Post:

How do I encode and decode a base64 string?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Get code errors & solutions at akashmittal.com
© 2022 Fix Code Error