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

The article

Query your table as a graph.Petabytes. Subsecond. No data movement.

Free downloadBook demo

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

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.

QuestionLEFT ANTI JOINLEFT SEMI JOINLEFT JOIN
Which left rows survive?Rows with no matchRows with a matchAll left rows
Which columns are available?Left onlyLeft onlyBoth sides
What happens with several right matches?The left row is excludedThe left row is returned once per input occurrenceOne 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?

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 and inline VALUES tables. Run it once to reproduce the examples:

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:

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_idjob_name
3rebuild_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:

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_idjob_name
2refresh_catalog
3rebuild_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:

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 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:

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 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:

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.

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

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 or Spark's <=>. 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 shows logical and physical plans; its join-planning guidance 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 or graph traversal over those recorded relationships.

PuppyGraph 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

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

Figure: Companies that use PuppyGraph.

Benefits of Elasticsearch Graph

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 and book a 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.

Hao Wu

Hao Wu

Software Engineer

Hao Wu is a Software Engineer with a strong foundation in computer science and algorithms. He earned his Bachelor’s degree in Computer Science from Fudan University and a Master’s degree from George Washington University, where he focused on graph databases.
Free downloadBook demo

Read next

All articles

Start free. Pay when it goes to production.

Contact us for any questions about capabilities, sizing and support.

* No payment required to start either one.

$ docker run -p 8081:8081 \
  -p 8182:8182 -p 7687:7687 \
  puppygraph/puppygraph:latestPulling puppygraph/puppygraph:latestStarting PuppyGraphWeb console listening on 0.0.0.0:8081Gremlin endpoint listening on 0.0.0.0:8182PuppyGraph is running# no pipelines · no second database · no data movement