SEMI-JOINS in SQL
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.
Enjoyed this? Here are some related articles you might find useful:
