Let’s not over complicate this!
What is the point of these joins?
Also known as the Cartesian Join in relational algebra. Here we compose all combination of tuples from the left and right tables.
SELECT * FROM [leftTable] CROSS JOIN [rightTable]
Notice that there’s no aliasing and no joining fields. We’re not performing matching - we’re just composing tuple combinations between the two tables.
SELECT * FROM [leftTable] AS left INNER JOIN [rightTable] AS right ON left.ID = right.ID;
OR if you prefer the old-fashioned way:
SELECT * FROM [leftTable] AS left, [rightTable] AS right WHERE left.ID = right.ID;
SELECT * FROM [leftTable] AS left LEFT JOIN [rightTable] AS right ON left.Id = right.ID;
These lack the syntactic sugar of the joins above. But funnily enough I find that the syntax of semi-joins tends to more natural human language.
Another big upside is that they are fast.
There are just two types of semi-joins that I want to show you.
LEFT SEMI JOIN
- Values in your joining field exist in both tables.
- You only want to return columns from the left table.
- Speed is a concern (when isn’t it?)
SELECT * FROM [leftTable] AS left WHERE EXISTS (SELECT 1 FROM [rightTable] WHERE left.ID = ID)
LEFT ANTI SEMI JOIN
Use case is same as the above except you’re returning rows where values in your joining field don’t match.
SELECT * FROM [leftTable] AS left WHERE NOT EXISTS (SELECT 1 FROM [rightTable] WHERE left.ID = ID)