thinQmaster Virtual testing partner Adaptive practice exams that find your weak spots and drill them. Every answer explained. From $69
Enroll

thinQmaster

Find your exam

Five questions free, before you decide.

Step 1 · choose a vendor

In one of our camps? Your practice exams come with the tuition — they are on your study page already.

Data & Analytics · Intermediate

Anti Join

A join pattern that returns rows from one table that have no match in another.

Definition

Anti joins are written with NOT EXISTS, LEFT JOIN ... WHERE right.key IS NULL, or (with NULL caveats) NOT IN. Spark and Databricks support LEFT ANTI JOIN directly. They are used to find orphans, new records or customers who never purchased.

Example

SELECT * FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

Exam tip

Prefer NOT EXISTS to NOT IN: if the subquery returns a NULL, NOT IN returns no rows.

Related terms