When working with databases, there are situations where you need to retrieve rows from one table that do not have corresponding values in another table. This can be achieved efficiently using either the NOT IN or LEFT JOIN approach.

✅ Using NOT IN

SELECT * 
FROM Table1
WHERE principal NOT IN (
  SELECT column_name
  FROM Table2
);

✅ Using LEFT JOIN

SELECT t1.* 
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.principal = t2.column_name
WHERE t2.column_name IS NULL;

Explanation

1. NOT IN Method: Directly filters out rows from Table1 where the principal column exists in Table2.

2. LEFT JOIN Method: Joins the tables and only keeps rows from Table1 that have no matching row in Table2 by checking for NULL values.

✅ When to Use Which Approach?

• NOT IN: Simple and readable but can be slower for large datasets.

• LEFT JOIN: More efficient for large datasets and allows more flexibility with additional conditions.

Conclusion

Both methods are effective for fetching rows from one table that are absent in another. The choice depends on the size of your data and performance requirements. The LEFT JOIN approach is generally preferred for handling large datasets due to better optimization.