A join ([LEFT] ANTI JOIN in SQL, anti or left_anti in PySpark) returning just the left-hand rows that find no partner on the right, with only the left table's columns. A typical use is spotting fact rows whose dimension key is missing.
Also called left anti join.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Anti join in context, with comparison tables and the common traps.
Terms in this definition
- JOIN
Combines rows from two or more tables in one SELECT, most often by pairing a primary key with the foreign key that refers to it.
- Serverless
Compute tier for single Azure SQL databases that scales automatically, pauses when idle and charges by the second. It is offered in General Purpose and Hyperscale, not Business Critical, and reserved capacity does not apply.
- Event
Table in Log Analytics where entries from Windows event logs are kept.
- Index field attributes
Settings applied to each field in an Azure AI Search index:
searchablefor full text,retrievableto return it,filterablefor exact-match$filter,sortable,facetablefor counts, andkeyfor the unique document ID.
Related terms
- Left anti join
A join that keeps only those rows from the left table that have no counterpart on the right. SQL spells it
[LEFT] ANTI JOIN, and PySpark calls itleft_anti. - Semi join
Returns the left-hand rows, and only their columns, for which the right-hand table holds a match. Its opposite, the anti join, returns left-hand rows lacking any match; both test for existence without adding the other side's columns.