SQL Basics: Joins

Let’s not over complicate this!

JOINS

What is the point of these joins?

CROSS JOIN

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.

INNER JOIN

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;

LEFT JOIN

SELECT
	*
FROM [leftTable] AS left
LEFT JOIN [rightTable] AS right
ON left.Id = right.ID;

SEMI-JOINS

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

Use case:

  • 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)

Updated: