Continuing our series about lesser-know types of SQL joins, let's look at the SEMI-JOIN today.

What does it do?

Well, we filter the entries in the left table to only the keys found in the right table, but unlike an INNER JOIN, we:
- we only get the columns in the left table
- even if there's multiple matching rows in the right table, we're not duplicating rows in the left one.

How are we going to implement it? We're going to use WHERE + EXISTS + a correlated sub-query (notice the WHERE clause in the subquery).

In the example below, we're using a semi-join to see which of the products have been previously ordered.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.