How to select rows with no matching entry in another table?

by

Here’s a simple query:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The key points are:

  1. LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

  2. The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null – in other words there is NO record in Table2 for that particular ID from Table1. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.

More Related Contents:

  1. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  2. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  3. When to use “ON UPDATE CASCADE”
  4. Can a foreign key be NULL and/or duplicate?
  5. Add Foreign Key relationship between two Databases
  6. Foreign key referring to primary keys across multiple tables?
  7. Can table columns with a Foreign Key be NULL?
  8. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  9. Designing an EAV database correctly for historical data
  10. Does Foreign Key improve query performance?
  11. How can I find which tables reference a given table in Oracle SQL Developer?
  12. SQL Server: Howto get foreign key reference from information_schema?
  13. How do I add a foreign key to an existing SQLite table?
  14. How can I INSERT data into two tables simultaneously in SQL Server?
  15. Generate Delete Statement From Foreign Key Relationships in SQL 2008?
  16. MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table?
  17. Delete parent if it’s not referenced by any other child
  18. Renaming foreign-key columns in MySQL
  19. What is causing Foreign Key Mismatch error?
  20. Foreign Key Creation issue in Oracle
  21. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  22. Why are foreign keys more used in theory than in practice?
  23. Is there a severe performance hit for using Foreign Keys in SQL Server?
  24. What is difference between foreign key and reference key?
  25. Foreign Key Not Populating with Primary Key Values
  26. Postgres and Indexes on Foreign Keys and Primary Keys
  27. can we have a foreign key which is not a primary key in any other table?
  28. GROUP BY behavior when no aggregate functions are present in the SELECT clause
  29. Query comparing dates in SQL
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment