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.
Also called LEFT SEMI JOIN.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Semi join in context, with comparison tables and the common traps.
Terms in this definition
- RETURNS
States what a function gives back, as a type or a table, inside
CREATE FUNCTION. SQL functions can leave it out and have the type worked out from their body. - Event
Table in Log Analytics where entries from Windows event logs are kept.
- MATCH
Used in WHERE when querying SQL Graph, it describes how to walk from node to node through edge tables, with patterns written like p1-(f1)->p2.
- Anti join
A join (
[LEFT] ANTI JOINin SQL,antiorleft_antiin 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.