When working with relational databases, you’ll often need to compare two tables to find records that exist in one table but not another. A common example is checking for articles that exist in a Family table but do not yet have a matching record in a mod_time table.
[Method 1 - LEFT JOIN / IS NULL]
This method returns every row from the first table and attempts to match rows in the second table. When no match exists, the joined columns are NULL, making it easy to identify missing records.
[SQK Server - LEFT JOIN / IS NUL]
CFFCS | CarrzSynEdit: | SQL Script 
SELECT f.* FROM Family c LEFT JOIN mod_time m ON f.FamID = m.FamID WHERE m.FamID IS NULL;

Use Case:
Best for readable queries and small-to-medium datasets.
[Method 2 - NOT EXISTS]
NOT EXISTS checks whether a matching row exists for each record. If no match is found, the record is returned.
This is generally the preferred method for large production databases because SQL Server can stop searching once a match is found.
[SQK Server - NOT EXISTS]
CFFCS | CarrzSynEdit: | SQL Script 
SELECT f.* FROM Family f WHERE NOT EXISTS (SELECT 1 FROM mod_time m WHERE m.FamID = f.FamID);

Use Case:
Preferred for large datasets and production environments due to higher performance.
[Method 3 - NOT IN]
NOT IN compares values against a list returned by a subquery. It is simple to write, but it should be used carefully because NULL values in the subquery can affect the results.
[SQK Server - NOT IN]
CFFCS | CarrzSynEdit: | SQL Script 
SELECT f.* FROM Family f WHERE f.FamID NOT IN
(SELECT m.FamIDFROM mod_time m);

Use Case:

Useful for simple comparisons when you're certain the compared column does not contain NULL values.
[Method 4: EXCEPT]
[SQK Server - EXCEPT]
EXCEPT returns rows from the first query that do not appear in the second query. It is excellent for auditing and comparing complete datasets, but it automatically removes duplicate rows.
CFFCS | CarrzSynEdit: | SQL Script 
SELECT FamID FROM Family 
EXCEPT SELECT FamID FROM mod_time;

Use Case:
Ideal for quickly identifying values that exist in one table but not another. Commonly used for auditing, validation, and comparing datasets.
[Which Should You Use?]
For most SQL Server applications, NOT EXISTS is the recommended choice
because it combines excellent performance with reliable handling of NULL
values. LEFT JOIN remains a great option when readability is more
important than squeezing out every bit of performance.