[sql] How to find rows in one table that have no corresponding row in another table

You can also use exists, since sometimes it's faster than left join. You'd have to benchmark them to figure out which one you want to use.

select
    id
from
    tableA a
where
    not exists
    (select 1 from tableB b where b.id = a.id)

To show that exists can be more efficient than a left join, here's the execution plans of these queries in SQL Server 2008:

left join - total subtree cost: 1.09724:

left join

exists - total subtree cost: 1.07421:

exists

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to optimization

Why does C++ code for testing the Collatz conjecture run faster than hand-written assembly? Measuring execution time of a function in C++ GROUP BY having MAX date How to efficiently remove duplicates from an array without using Set Storing JSON in database vs. having a new column for each key Read file As String How to write a large buffer into a binary file in C++, fast? Is optimisation level -O3 dangerous in g++? Why is processing a sorted array faster than processing an unsorted array? MySQL my.cnf performance tuning recommendations

Examples related to h2

Spring Boot default H2 jdbc connection (and H2 console) auto increment ID in H2 database How to run H2 database in server mode? H2 database error: Database may be already in use: "Locked by another process" View content of H2 or HSQLDB in-memory database H2 in-memory database. Table not found Can I have H2 autocreate a schema in an in-memory database? Frontend tool to manage H2 database How to find rows in one table that have no corresponding row in another table