MS SQL Server Advanced JOINS

Hello friends, welcome to shrash studio learning, in this article we will take the next step after basic joins and learn how to retrieve only the non-matching rows — rows from the left table that don't match, rows from the right table that don't match, and rows that don't match on either side. These are known as anti-joins, and they are used every day for data reconciliation, orphan detection, and "find me everyone who didn't" queries. Simple English, real examples, and the one SQL rule you must never forget — never use = NULL.

Quick Recap of Basic Joins

Before we jump into anti-joins, let's quickly remind ourselves what the standard joins return:

Join Type	Returns
Inner Join	Only matching rows from both tables
Left Join	All left rows + matches from right (NULL for non-matches)
Right Join	All right rows + matches from left (NULL for non-matches)
Full Outer Join	Everything from both tables (NULLs on unmatched sides)

Each of these includes the matching rows. In this article we flip the question — how do we get only the non-matching rows?

Our Sample Tables

We'll use two tables — Customers and Orders — linked by CustomerId. This is the classic reconciliation scenario.


CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    CustName   VARCHAR(60),
    City       VARCHAR(30)
);

CREATE TABLE Orders (
    OrderId    INT PRIMARY KEY,
    Product    VARCHAR(40),
    Amount     DECIMAL(10, 2),
    CustomerId INT NULL
);

INSERT INTO Customers VALUES
(1, 'Ravi Sharma',    'Mumbai'),
(2, 'Nisha Patel',    'Delhi'),
(3, 'Arun Gupta',     'Bangalore'),
(4, 'Pooja Desai',    'Pune'),       -- never placed an order
(5, 'Suresh Reddy',   'Chennai');    -- never placed an order

INSERT INTO Orders VALUES
(101, 'Laptop',     55000, 1),
(102, 'Keyboard',    2500, 2),
(103, 'Monitor',    18000, 3),
(104, 'Mouse',        800, 1),
(105, 'Webcam',      3500, NULL);    -- orphan order (no customer)

Key things to notice in the data:

Matching rows: 4 orders (101–104) belong to real customers (1, 2, 3).

Non-matching customers: Pooja Desai and Suresh Reddy never placed any order.

Non-matching order: Order 105 (Webcam) has NULL CustomerId — an orphan.

The Big Idea: Join + Filter on NULL

The trick for every anti-join is the same — take the outer join you would normally write, then filter out the matching rows by looking for NULL on the joined side.

THE ANTI-JOIN PATTERN

Left table only

LEFT JOIN + WHERE right.key IS NULL

Right table only

RIGHT JOIN + WHERE left.key IS NULL

Both tables

FULL JOIN + WHERE left.key IS NULL OR right.key IS NULL

Let's see all three in action.

1. Non-Matching Rows from the Left Table (Left Anti-Join)

"Find all customers who have never placed an order."

Start with a regular LEFT JOIN — customers on the left, orders on the right. That gives us all customers, with NULL in the order columns for those who never ordered. Then filter for exactly those NULLs.


SELECT c.CustomerId, c.CustName, c.City
FROM   Customers c
LEFT JOIN Orders o
       ON c.CustomerId = o.CustomerId
WHERE  o.OrderId IS NULL;

Result — 2 rows:

CustomerId	CustName	City
4	Pooja Desai	Pune
5	Suresh Reddy	Chennai

We check o.OrderId IS NULL because OrderId is the primary key of the right table — it can never really be NULL, so if it is NULL in our result, it means the LEFT JOIN couldn't find a matching order for that customer.

  Always check NULL on the right table's primary key (not the foreign key column on the left). The primary key never legitimately carries NULL, so a NULL there is a guaranteed "no match" signal.

2. Non-Matching Rows from the Right Table (Right Anti-Join)

"Find all orders that are not linked to any customer" — classic data-quality check.

Use RIGHT JOIN keeping Orders as the right table, then filter for rows where the customer side is NULL.


SELECT o.OrderId, o.Product, o.Amount
FROM   Customers c
RIGHT JOIN Orders o
       ON c.CustomerId = o.CustomerId
WHERE  c.CustomerId IS NULL;

Result — 1 row:

OrderId	Product	Amount
105	Webcam	3500.00

This is an orphan order — somehow an order got created without a valid customer link. Queries like this are run nightly in many real systems to catch broken data.

3. Non-Matching Rows from Both Tables (Full Anti-Join)

"Find every mismatch anywhere — customers with no orders and orders with no customers."

Start from a FULL OUTER JOIN and filter rows where either side is NULL.


SELECT c.CustomerId, c.CustName, o.OrderId, o.Product
FROM   Customers c
FULL OUTER JOIN Orders o
       ON c.CustomerId = o.CustomerId
WHERE  c.CustomerId IS NULL
   OR  o.OrderId IS NULL;

Result — 3 rows:

CustomerId	CustName	OrderId	Product
4	Pooja Desai	NULL	NULL
5	Suresh Reddy	NULL	NULL
NULL	NULL	105	Webcam

All three mismatches in one clean report. This is the most complete data-quality check you can run against a pair of tables.

Step-by-Step: What the Query Engine Actually Does

Left Anti-Join Execution (customers with no orders)

1. FROM → Read Customers (the left table)

2. LEFT JOIN → Attach matching Orders rows; NULL where no match

3. WHERE → Keep only rows where o.OrderId IS NULL

4. SELECT → Project Customer columns only (Orders side is NULL anyway)

5. Return → Pooja Desai and Suresh Reddy

The Golden NULL Rule

Never use = NULL or != NULL in SQL Server.

NULL means "unknown", and two unknowns are never considered equal. A comparison like WHERE x = NULL always evaluates to UNKNOWN and no rows are returned — even if x really is NULL. Always use IS NULL or IS NOT NULL.


-- Wrong: returns zero rows, no error
SELECT * FROM Orders WHERE CustomerId = NULL;

-- Right: returns the orphan order
SELECT * FROM Orders WHERE CustomerId IS NULL;

An Alternative: NOT EXISTS

Anti-joins can also be written with NOT EXISTS. For "customers with no orders":


SELECT c.CustomerId, c.CustName, c.City
FROM   Customers c
WHERE  NOT EXISTS (
           SELECT 1
           FROM   Orders o
           WHERE  o.CustomerId = c.CustomerId
       );

Both styles work. Many developers find NOT EXISTS more readable and, in SQL Server, the optimizer usually produces an equally fast plan for both. Pick whichever reads more clearly for your team.

Side-by-Side Summary

Goal	Join + Filter	Result
All customers + their orders	`LEFT JOIN` (no filter)	All customers, orders where they exist
Customers with no orders	`LEFT JOIN` + `WHERE o.OrderId IS NULL`	Non-matching left rows only
Orders with no customer	`RIGHT JOIN` + `WHERE c.CustomerId IS NULL`	Non-matching right rows only
Every mismatch, both sides	`FULL JOIN` + `WHERE ... IS NULL OR ... IS NULL`	Orphans from both tables
Only matching pairs	`INNER JOIN`	Matches only

When Would You Actually Use This?

REAL-WORLD ANTI-JOIN USE CASES

Inactive users

Users who never logged in / never purchased

Orphan records

Child rows whose parent got deleted

Empty categories

Categories or tags that contain no items

Missing translations

Products without a row in the translations table

Data reconciliation

Comparing two systems to find missing records

Cleanup scripts

Find rows to archive before deletion

Common Mistakes

1. Checking the wrong column for NULL. Check NULL on the primary key of the joined table, not the foreign key. The foreign key might legitimately be NULL even when a join would succeed in some other scenario.

2. Using = NULL. It compiles but silently returns nothing. Always use IS NULL.

3. Putting the filter in the ON clause. ON c.CustomerId = o.CustomerId AND o.OrderId IS NULL does not behave like a WHERE filter on an outer join — it changes which rows match. For anti-joins, the IS NULL check always belongs in WHERE.

4. Forgetting to use OUTER join. An INNER JOIN throws away the non-matching rows before you can filter them. The whole anti-join trick depends on outer-join NULLs existing.

5. Writing NOT IN with a nullable column. WHERE CustomerId NOT IN (SELECT CustomerId FROM Orders) silently returns zero rows if the subquery has even one NULL. Use NOT EXISTS or the LEFT JOIN + IS NULL pattern instead.

The Interview Answer

If an interviewer asks "How do you find rows that exist in one table but not the other?":

"I use an anti-join pattern — a LEFT JOIN from the source table to the target table, then a WHERE clause that checks IS NULL on the target's primary key. The LEFT JOIN brings all left rows in with NULLs on the unmatched right side, and the NULL filter keeps only those non-matching rows. If I need mismatches on both sides, I use a FULL OUTER JOIN with IS NULL on either side. The same result can be written with NOT EXISTS for better readability. And I never write = NULL — it always returns no rows."

Summary

Anti-joins answer the "who didn't?" question — which customers never bought, which products were never reviewed, which orders got orphaned. The pattern is always the same: pick the outer join that keeps the rows you care about, then add a WHERE clause that looks for NULL on the joined table's primary key.

Left anti-join (LEFT JOIN + IS NULL on right) gets you unmatched left rows. Right anti-join (RIGHT JOIN + IS NULL on left) gets you unmatched right rows. Full anti-join (FULL JOIN + IS NULL on either side) gets you everything that doesn't line up. And remember the one golden rule — in SQL Server, NULL is never equal to anything, including itself. Always write IS NULL, never = NULL.

Concept	Key Takeaway
Anti-join idea	Outer join + NULL filter isolates non-matches
Left anti-join	`LEFT JOIN` + `WHERE right.key IS NULL`
Right anti-join	`RIGHT JOIN` + `WHERE left.key IS NULL`
Full anti-join	`FULL JOIN` + `IS NULL` on either side
Which column to null-check	The joined table's primary key (never the FK)
NULL comparison	Always `IS NULL`, never `= NULL`
Alternative style	`NOT EXISTS (SELECT 1 FROM ... WHERE ...)`
Avoid	`NOT IN` when the subquery may contain NULLs
Filter placement	Always in `WHERE`, not in `ON`
Typical use	Orphan detection, inactive users, data reconciliation

License Key