# LEFT ANTI JOIN in SQL: Syntax, Examples, and Uses

> Learn how LEFT ANTI JOIN works in SQL, explore syntax and examples, compare it with LEFT SEMI JOIN, NOT EXISTS, and NOT IN, and understand common use cases.

- Canonical URL: https://www.puppygraph.com/blog/left-anti-join
- Published: 2026-11-20
- Author: Hao Wu

Finding missing records depends on defining what counts as a match. A scheduled job with no recorded run needs different attention from a job that ran but never succeeded. LEFT ANTI JOIN expresses both questions; the condition determines which absence you detect.

This guide explains anti-join behavior through a small job-monitoring example, then shows native SQL syntax, equivalent patterns, and the NULL handling that can change your results. It also compares LEFT ANTI JOIN with LEFT SEMI JOIN and explains when NOT IN answers a different question.

## What is a LEFT ANTI JOIN in SQL?

A LEFT ANTI JOIN returns rows from the left input for which no row in the right input satisfies the join condition. It returns columns from the left side only. If even one qualifying right row exists, that left row is excluded.

A LEFT SEMI JOIN keeps the complementary group: left rows with at least one qualifying match. Both operations filter left rows by existence. A regular LEFT JOIN instead produces matching pairs and preserves unmatched left rows with NULLs on the right. [Apache Spark documents all three join types](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-join.html).

| **Question** | **LEFT ANTI JOIN** | **LEFT SEMI JOIN** | **LEFT JOIN** |
| --- | --- | --- | --- |
| Which left rows survive? | Rows with no match | Rows with a match | All left rows |
| Which columns are available? | Left only | Left only | Both sides |
| What happens with several right matches? | The left row is excluded | The left row is returned once per input occurrence | One row is produced per matching pair |

Neither a semi join nor an anti join deduplicates the left input. If two identical left rows have no match, both survive the anti join. Choose an existence test when you need to identify qualifying records without generating every matching pair.

## How does LEFT ANTI JOIN work?

Consider two datasets: `jobs` lists scheduled jobs, and `job_runs` records their attempts within one reporting window. Each job has an ID; each run optionally references that ID.

For each job, the logical question is whether any run satisfies the complete join condition. A match removes the job from the result. No match retains it. An empty right input therefore retains every left row. This describes the result, not a requirement that the engine execute a separate scan for every job.

The condition can include more than ID equality. Matching on ID alone finds jobs with no attempts. Adding `status = 'succeeded'` finds jobs with no successful attempts, including jobs whose attempts all failed.

![How does LEFT ANTI JOIN work?](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/a09a5985-71d4-49ab-bfbe-aba85b5c78ce/hao-wu-20261120-63073c9bda1ad787c9236790.png)

Figure: Jobs 2 and 3 survive because neither has a successful run; multiple matches for job 1 and a NULL run key do not change that result.

## LEFT ANTI JOIN syntax in SQL

The following setup uses Spark SQL [temporary views](https://spark.apache.org/docs/latest/sql-ref-syntax-ddl-create-view.html) and [inline VALUES tables](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-inline-table.html). Run it once to reproduce the examples:

```sql
CREATE OR REPLACE TEMP VIEW jobs AS
SELECT * FROM VALUES
  (1, 'extract_inventory'),
  (2, 'refresh_catalog'),
  (3, 'rebuild_search'),
  (4, 'archive_logs')
AS t(job_id, job_name);

CREATE OR REPLACE TEMP VIEW job_runs AS
SELECT * FROM VALUES
  (101, 1, 'succeeded'),
  (102, 1, 'succeeded'),
  (103, 2, 'failed'),
  (104, 4, 'failed'),
  (105, 4, 'succeeded'),
  (106, CAST(NULL AS INT), 'succeeded')
AS t(run_id, job_id, status);
```

Spark SQL supports the native join form:

```sql
SELECT j.job_id, j.job_name
FROM jobs AS j
LEFT ANTI JOIN job_runs AS r
  ON j.job_id = r.job_id
ORDER BY j.job_id;
```

Here, `j` supplies the candidate jobs and `r` supplies possible matches. The `ON` clause defines a match; the select list names the left-side columns to return. `ORDER BY` makes the displayed results deterministic.

The anti-join operation is more portable than this keyword spelling. In PostgreSQL, use NOT EXISTS or LEFT JOIN with IS NULL, as shown below, against equivalent tables. The setup above uses Spark SQL syntax; adapt the temporary views to your database's dialect.

## LEFT ANTI JOIN examples

**Jobs with no recorded runs.** The preceding query returns:

| **job\_id** | **job\_name** |
| --- | --- |
| 3   | rebuild\_search |

Job 2 is excluded because its failed attempt still matches the ID condition. Job 4 also has attempts, regardless of their outcomes. This query identifies missing activity, not unsuccessful activity.

**Jobs with no successful runs.** Add the outcome requirement to the match condition:

```sql
SELECT j.job_id, j.job_name
FROM jobs AS j
LEFT ANTI JOIN job_runs AS r
  ON j.job_id = r.job_id
 AND r.status = 'succeeded'
ORDER BY j.job_id;
```

The result now includes two jobs:

| **job\_id** | **job\_name** |
| --- | --- |
| 2   | refresh\_catalog |
| 3   | rebuild\_search |

Job 2 has only a failed attempt, while job 3 has none. Job 4 is excluded because its successful attempt is sufficient, even though another attempt failed. Job 1's two successes have the same exclusion effect as one.

Replacing `LEFT ANTI JOIN` with `LEFT SEMI JOIN` in this query returns jobs 1 and 4, once each. That is the set with evidence of success.

The same pattern supports source-to-target reconciliation by returning source records absent from the target. Reversing the inputs finds unexpected target records. For reference checks, place child records on the left and parent records on the right to find missing parents. Decide separately whether a NULL child reference represents an error or an optional relationship.

In operational queries, scope the right input to the intended time window. Otherwise, an old successful run could exclude a job that has no success in the period you are investigating.

## How to perform LEFT ANTI JOIN using NOT EXISTS

NOT EXISTS expresses the no-success question without a dedicated anti-join keyword:

```sql
SELECT j.job_id, j.job_name
FROM jobs AS j
WHERE NOT EXISTS (
  SELECT 1
  FROM job_runs AS r
  WHERE r.job_id = j.job_id
    AND r.status = 'succeeded'
)
ORDER BY j.job_id;
```

This returns jobs 2 and 3. The subquery is correlated through `r.job_id = j.job_id`, so it asks about the current job. NOT EXISTS is true when that subquery returns no rows.

The `1` is a convention: the query checks for a row's existence rather than using its projected value. It does not count matching rows. PostgreSQL's [EXISTS documentation](https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS) explains this distinction.

Keep the whole business key inside the correlation. If job IDs are unique only within an environment, compare both `environment_id` and `job_id`. Omitting the environment could let a successful development run hide a missing production run. The SQL may execute correctly while answering the wrong operational question.

## How to perform LEFT ANTI JOIN using LEFT JOIN and IS NULL

A left outer join can preserve the jobs and expose which ones lack a qualifying run:

```sql
SELECT j.job_id, j.job_name
FROM jobs AS j
LEFT JOIN job_runs AS r
  ON j.job_id = r.job_id
 AND r.status = 'succeeded'
WHERE r.job_id IS NULL
ORDER BY j.job_id;
```

This also returns jobs 2 and 3. The outer join supplies NULLs for right-side columns when no successful run matches. The WHERE clause retains those rows.

The tested column must distinguish an unmatched row from a matched row. Here, `r.job_id` is safe because an ordinary equality match cannot have a NULL join key. More generally, a right-side primary key or another guaranteed non-null column is a useful marker. Testing a nullable field such as `r.error_message` could retain a real match whose message happens to be missing.

Keep `r.status = 'succeeded'` in `ON`. Moving it into `WHERE` alongside `r.job_id IS NULL` removes the unmatched rows: their status is NULL, so they cannot satisfy that comparison. PostgreSQL's [outer-join examples](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-JOIN) illustrate why ON and WHERE are not interchangeable here.

NOT EXISTS often makes the intent easier to read because it expresses the existence test directly. Both forms require the same care in defining qualifying matches.

## Handling NULL values in LEFT ANTI JOIN

With ordinary equality, NULL does not equal another value, including another NULL. A NULL comparison evaluates to unknown. Consequently, run 106's missing job ID matches none of the scheduled jobs; it does not prevent the native anti join or correlated NOT EXISTS from returning jobs 2 and 3.

NOT IN has different behavior:

```sql
SELECT j.job_id, j.job_name
FROM jobs AS j
WHERE j.job_id NOT IN (
  SELECT r.job_id
  FROM job_runs AS r
  WHERE r.status = 'succeeded'
)
ORDER BY j.job_id;
```

This returns no rows. The subquery produces `1, 1, 4, NULL`. Under standard SQL NULL semantics, NOT IN is false for jobs 1 and 4 because an equal value exists. For jobs 2 and 3, it is unknown because the list contains NULL and no equal value. WHERE keeps only true results. PostgreSQL documents these [NOT IN semantics](https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-NOTIN).

Because every left ID in this example is non-null, excluding NULLs from the subquery restores the intended result:

```sql
SELECT j.job_id, j.job_name
FROM jobs AS j
WHERE j.job_id NOT IN (
  SELECT r.job_id
  FROM job_runs AS r
  WHERE r.status = 'succeeded'
    AND r.job_id IS NOT NULL
)
ORDER BY j.job_id;
```

Jobs 2 and 3 return again. This repair does not settle nullable left keys: with a nonempty right result, a NULL left key still fails the NOT IN filter, while an equality-based anti join retains it.

Choose the intended NULL policy explicitly. Add a left-side `IS NOT NULL` filter if missing keys should be excluded. If two NULL keys should count as a match, use a null-safe comparison, such as PostgreSQL's [`IS NOT DISTINCT FROM`](https://www.postgresql.org/docs/current/functions-comparison.html) or Spark's [`<=>`](https://spark.apache.org/docs/latest/sql-ref-null-semantics.html#comparison-operators). With a null-safe join, test a separate non-null right-side marker in the LEFT JOIN pattern; the nullable join key can no longer identify unmatched rows reliably.

## Common LEFT ANTI JOIN errors and how to fix them

**Using unsupported syntax.** A syntax error near `ANTI` may reflect the SQL dialect. Use NOT EXISTS when the engine does not accept LEFT ANTI JOIN.

**Forgetting the correlation.** Removing `r.job_id = j.job_id` from the subquery asks whether any successful run exists anywhere. In this fixture, one does, so every job is excluded. Check that the subquery references the intended outer row.

**Using inequality to find absence.** Joining on `j.job_id <> r.job_id` finds pairs with different IDs. A job can have many such pairs and still have a matching run. Use an existence test with the actual matching condition.

**Expecting automatic deduplication.** Repeated left rows remain repeated when they qualify. Check the input's grain before adding DISTINCT; a duplicate job may indicate an earlier join multiplied the input.

**Assuming one spelling is always faster.** Compare equivalent queries with representative data and inspect the plan. Spark's [`EXPLAIN EXTENDED`](https://spark.apache.org/docs/latest/sql-ref-syntax-qry-explain.html) shows logical and physical plans; its [join-planning guidance](https://spark.apache.org/docs/latest/sql-performance-tuning.html) explains how statistics and join strategies affect execution.

Do not infer a cause from an absence result. The query identifies jobs without success. If you maintain dependency tables, the next investigation might follow `rebuild_search` through its prerequisite jobs to find unsuccessful upstream runs. An unknown number of prerequisite levels calls for [recursive SQL](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE) or graph traversal over those recorded relationships.

[PuppyGraph](https://www.puppygraph.com/) is the first and only zero-ETL graph query engine that queries existing tables as a graph without loading them into a separate graph database. Its distributed engine reads tables in place in SQL databases, data warehouses, and data lakes or lakehouses, including direct reads of open table formats like Iceberg and Delta Lake. Connect the sources, define a graph schema mapping tables to nodes and edges, and query with openCypher and Gremlin; job-prerequisite relationships can become paths to explore while the same tables remain queryable with SQL.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/d0e40fcf-cf5a-40b7-97c1-5f5be25de9b6/hao-wu-20261120-4b2587a755bbd5c9068284ee.png)

Figure: PuppyGraph queries the existing tables in place, so graph and SQL queries share one copy of the data and no pipeline keeps a graph copy in sync.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/37a756bf-deab-4cae-b1f3-aecb24bff3b1/hao-wu-20261120-5820007cae8f617f5bb57a14.png)

Figure: Companies that use PuppyGraph.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/5f9c5853-ffc3-40c1-9bfe-b097ccf4296c/hao-wu-20261120-4492653ad4ba877e25923f1c.png)

Figure: Engineering teams at PuppyGraph customers on what they use it for.

## Conclusion

LEFT ANTI JOIN keeps left rows without a qualifying right-side match. Define that match carefully, including status, time window, and every part of the business key. NOT EXISTS expresses the same operation across SQL dialects, while LEFT JOIN with IS NULL works with a reliable unmatched-row marker. Use NOT IN only with a deliberate policy for NULLs on both sides.

Try the forever-free [PuppyGraph Developer Edition](https://www.puppygraph.com/download-confirmation) and [book a demo](https://www.puppygraph.com/book-demo) with the team to see how openCypher and Gremlin queries trace relationships across warehouse and lakehouse tables, with no graph-specific ETL, when investigating missing runs leads to job-prerequisite paths.
