Every SQL Join You'll Ever Need

08 Nov 2018 🔖 databases sql
💬 EN

All right … maybe not every join. But this cheat sheet helped me score 104% on my Database 101 midterm exam, so it must be worth something.

It took a bit of work to find my old school notes, because I hadn’t even put “join” in the title. Instead, I called my notes “Set Operations In SQL.” And that’s really what this is all about, because it doesn’t matter if you use SQL, Excel VLOOKUP, Python, or a pen and paper to compare one list of records to another. Combining one “set” of things with another, as a business problem, is math. Better yet, it’s fun math, not hard math! It’s Venn diagrams – the kind of math that gets you doodling like you’re in elementary school.

SQL code is useful to know when the two sets of records (each “record” representing some real-world entity, like a person, an invoice, or a country) that your business problem requires you to combine both exist within a single relational database management system (RDBMS), usually simply referred to as a “database.” All such systems come with the ability to extract your data using the SQL programming language.

I do a lot of work in Oracle databases, so I find Oracle-flavored SQL the fastest to type and will use it as an example. You can run Oracle SQL code online if you sign up for a free account and try their Live SQL tool.

To translate this into another “flavor” of SQL, you may need to Google the keywords I’ve used, together with the name of your database. For example, when my full outer join command wouldn’t work in a MySQL database, I Googled “‘full outer join’ mysql” and discovered that MySQL-flavored SQL doesn’t include a full outer join command. Instead, you have to get clever combining smaller set operations (it’s like carefully coloring in your Venn diagram one entire circle at a time, rather than running your crayon over the whole diagram at once).

Our Sample Data

Table “A” Contents (CONVENTION)

CONVENTION is a list of people who attended a convention and where they work.

(Yes, it’s a very nosy event, asking its attendees for social security number.)

Table “B” Contents (RESTAURANT)

RESTAURANT is a list of regular customers at a restaurant, their favorite food, and their age.

(Yes, it’s a very nosy restaurant, asking its regular customers for social security number.)

• Note the “ccc,” rather than “333,” email address for Cathy Combs.
• Note the typo in the social security number for Frances Fulvia.

Code To Create This Data (“DDL” & “DML”)

Here’s code to create CONVENTION and RESTAURANT tables in an Oracle database and populate them with our sample data, if you want to try it yourself.

drop table CONVENTION;
create table CONVENTION ( 
	ssn		varchar2(6) not null 
	, name_lf	varchar2(30) not null 
	, ph		varchar2(7) not null 
	, em		varchar(15) not null 
	, company	varchar(15) not null 
);
insert into CONVENTION values ('A1A1A1', 'Amjit, Anush', '1111111', '[email protected]', 'Apple');
insert into CONVENTION values ('B2B2B2', 'Borges, Benita', '2222222', '[email protected]', 'Boiron');
insert into CONVENTION values ('C3C3C3', 'Combs, Cathy', '3333333', '[email protected]', 'CVS');
insert into CONVENTION values ('D4D4D4', 'Daher, Darweesh', '4444444', '[email protected]', 'Dell');
insert into CONVENTION values ('E5E5E5', 'Ellis, Ezra', '5555555', '[email protected]', 'EDF');
insert into CONVENTION values ('F6F6F6', 'Fulvia, Frances', '6666666', '[email protected]', 'Firestone');
drop table RESTAURANT;
create table RESTAURANT ( 
	social		varchar2(6) not null 
	, name_fl	varchar2(30) not null 
	, phone		varchar2(7) not null 
	, email		varchar2(15) not null 
	, fav_food	varchar2(10) not null 
	, age		int not null 
);
insert into RESTAURANT values ('C3C3C3', 'Cathy Combs', '3333333', '[email protected]', 'Carrots', 33);
insert into RESTAURANT values ('D4D4D4', 'Darweesh Daher', '4444444', '[email protected]', 'Doritos', 44);
insert into RESTAURANT values ('E5E5E5', 'Ezra Ellis', '5555555', '[email protected]', 'Endives', 55);
insert into RESTAURANT values ('FFF666', 'Frances Fulvia', '6666666', '[email protected]', 'Fries', 66);
insert into RESTAURANT values ('G7G7G7', 'Grace Gao', '7777777', '[email protected]', 'Garlic', 77);
insert into RESTAURANT values ('H8H8H8', 'Helen Hopper', '8888888', '[email protected]', 'Hummus', 88);

Who overlaps and how

Let’s say we’re a large corporation. We’re so large that we run the convention and we own the restaurant. We want to look for “overlaps” between our two customer lists an analyze them accordingly.

Actually, that’s all the context I’ll give about “why” I chose the queries and output I chose. They make no business sense at all. I chose them to demonstrate what the different styles of SQL query can and can’t do!

But back to the business of combining our sets:

As always when comparing “people” records from two different data sets, decisions must be made about what “proves” two records represent the same “real-world person.” Do you match on name? Email? Social security number (remember, they are re-used after people die)? Some combination thereof?

For our examples, we’ll look at two different ways of combining our data, just to flex our code muscles.

Before reading the code, be sure to look at CONVENTION and RESTAURANT and take note that:

1. When we join on A.ssn=B.social, Cathy-Ezra (3-5) match
   • Anush, Benita, and “Frances w/ ssn F6F6F6” remain unique to CONVENTION
   • “Frances w/ ssn FFF666,” Grace, & Helen remain unique to RESTAURANT
2. When we join on A.ph=B.phone and A.em=B.email, Darweesh-Frances (4-6) match
   • Anush, Benita, and “Cathy w/ em 333@…” remain unique to CONVENTION
   • “Cathy w/ email ccc@…,” Grace, & Helen remain unique to RESTAURANT

Join Type: “In A”

For this join type, we’d expect 6 rows of output (Anush-Frances – all people from CONVENTION).

To the extent that any of these people “also exist” in RESTAURANT (as defined by the “matching criteria” we choose), we’d expect to see details about them from RESTAURANT.

LEFT OUTER JOIN

SQL Query, left outer join example 1/4

First, we’ll match on social security number, so Cathy has details from “table B” but Frances doesn’t.

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
left outer join RESTAURANT B on A.ssn = B.social;

Query Output, left outer join example 1/4

SQL Query, left outer join example 2/4

Next, we’ll match on phone and email, so Frances has details from “table B” but Cathy doesn’t.)

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
left outer join RESTAURANT B on A.ph = B.phone and A.em = B.email;

Query Output, left outer join example 2/4

SQL Query, left outer join example 3/4

Next, we’ll try (incorrectly) to suppress name_fl and fav_food details for anyone younger than 50.

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
left outer join RESTAURANT B on A.ssn = B.social
where B.age >= 50;

Query Output, left outer join example 3/4

Note

SQL Query, left outer join example 4/4

Now we’ll actually suppress name_fl and fav_food details for anyone 50 or younger.

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
left outer join (select * from RESTAURANT where age >= 50) B on A.ssn = B.social;

Query Output, left outer join example 4/4

Note

Join Type: “In A But Not In B”

For this join type, we’d expect 3 rows of output (Anush+Benita+Cathy or Anush+Benita+Frances, depending on the “matching criteria” we choose).

NOT IN

SQL Query, not in example 1/3

not in is great when you’re only matching on one column.

select A.name_lf, A.company
from CONVENTION A
where A.ssn not in ( select B.social from RESTAURANT B );

Query Output, not in example 1/3

SQL Query, not in example 2/3

not in gets dicey when you need to match on multiple columns – you have to concatenate multiple columns into a single column and responsibly handle blank/null values.

It is NOT PREFERRED for matching on multiple columns. Use NOT EXISTS instead.

This query is just for show!

select A.name_lf, A.company
from CONVENTION A
where nvl(A.ph,'')||'~'||nvl(A.em,'') not in ( select nvl(B.phone,'')||'~'||nvl(B.email,'') from RESTAURANT B );

Query Output, not in example 2/3

SQL Query, not in example 3/3

Our third not in example isn’t even a real join between tables: note that RESTAURANT isn’t even in the query.

I just wanted to point out that this is typed the same way as the not in that you can use when adding a hand-typed value filter to a query.

select A.name_lf, A.company
from CONVENTION A
where A.ssn not in ( 'B2B2B2','XYZZY','E5E5E5' );

Query Output, not in example 3/3

<> ALL

This is exactly like not in, only you type <> all instead. Every example from the section on not in could have been written with <> all.

SQL Query

select A.name_lf, A.company
from CONVENTION A
where A.ssn <> all ( select B.social from RESTAURANT B );

Query Output

MINUS

With minus, we can’t output columns that aren’t in our “match criteria.”

That said, such limited output might suffice if our business problem is a simple “dummy check” list of “all values that appear only in A.”

SQL Query, minus example 1/2

select A.ssn
from CONVENTION A
minus
select B.social
from RESTAURANT B;

Query Output, minus example 1/2

SQL Query, minus example 2/2

select A.ph, A.em
from CONVENTION A
minus
select B.phone, B.email
from RESTAURANT B;

Query Output, minus example 2/2

NOT EXISTS

where not exists (...) is the most useful syntax for writing a complex query with “matching criteria” that involve a multi-column match.

This approach supports “further filters” on A – just include them as additional and and or clauses beyond the where clause responsible for the “matching” operation.

If the rest of your filters have or in them, be sure to use parentheses to ensure that you don’t accidentally make your where not exists (...) optional.

SQL Query

SELECT A.name_lf, A.company
  FROM CONVENTION A
WHERE NOT EXISTS (
  SELECT NULL
    FROM RESTAURANT B
  WHERE A.ph = B.phone and A.em = B.email
);

Query Output

LEFT OUTER JOIN ... B.... IS NULL

I can’t think of a single reason to use the “LEFT OUTER JOIN plus B.joinColumn IS NULL” syntax in practice. It’s completely overwrought.

But … it’s conceptually related to a common approach to “In A Or B, But Not Both” so it’s an interesting mental exercise to make sure you understand.

It’s also a good “cheat” pattern to be familiar with when you run into a report-writing tool that doesn’t have a NOT EXISTS syntax, like Cognos Report Studio or Python+Pandas.

This approach supports “further filters” on A – just include them as additional and and or clauses beyond the where clause responsible for the “matching” operation.

If the rest of your filters have or in them, be sure to use parentheses to ensure that you don’t accidentally make your where ... is null filter(s) optional.

SQL Query

select A.name_lf, A.company
from CONVENTION A
left outer join RESTAURANT B on A.ph = B.phone and A.em = B.email
where B.phone is null
and B.email is null;

Query Output

Join Type: “In A, B, Or Both”

For this join type, we’d expect 9 rows of output (Anush-Helen, with Cathy or Frances doubled up depending on which “matching criteria” we choose).

UNION

SQL Query, union example 1/2

select A.ssn
from CONVENTION A
union
select B.social
from RESTAURANT B;

Query Output, union example 1/2

• Do you see the doubled-up “Frances” data?

Note

As with minus, we can’t output columns that weren’t in our “match criteria.”

A single column of output might suffice if our business problem is a simple “dummy check” list of “all possible values from either table.”

SQL Query, union example 2/2

select A.ph, A.em
from CONVENTION A
union
select B.phone, B.email
from RESTAURANT B;

Query Output, union example 2/2

FULL OUTER JOIN

SQL Query, full outer join example 1/2

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
full outer join RESTAURANT B on A.ssn = B.social;

Query Output, full outer join example 1/2

• Do you see the doubled-up “Frances” data?

SQL Query, full outer join example 2/2

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
full outer join RESTAURANT B on A.ph = B.phone and A.em = B.email;

Query Output, full outer join example 2/2

• Do you see the doubled-up “Cathy” data?

MySQL fake FULL OUTER JOIN

This is the clever trickery I mentioned earlier to compensate for the fact that MySQL-flavored SQL doesn’t have a full outer join command.

SQL Query

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
left outer join RESTAURANT B on A.ssn = B.social
union
select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
right outer join RESTAURANT B on A.ssn = B.social;

Query Output

• Do you see the doubled-up “Frances” data?

Join Type: “In Both A And B”

For this join type, we’d expect 3 rows of output (Cathy-Ezra or Darweesh-Frances, depending on the “matching criteria” we choose).

IN

SQL Query, in example 1/3

select A.name_lf, A.company
from CONVENTION A
where A.ssn in ( select B.social from RESTAURANT B );

Query Output, in example 1/3

Note

• in is great when:
  • you’re only matching on one column, and
  • you don’t need to see, or filter further on, any details from B

SQL Query, in example 2/3

select A.name_lf, A.company
from CONVENTION A
where nvl(A.ph,'')||'~'||nvl(A.em,'') in ( select nvl(B.phone,'')||'~'||nvl(B.email,'') from RESTAURANT B );

Query Output, in example 2/3

Note

• in gets dicey when you need to match on multiple columns – you have to concatenate multiple columns into a single column and responsibly handle blank/null values. It is NOT PREFERRED for matching on multiple columns. Use exists instead. This is just for show!

SQL Query, in example 3/3

select A.name_lf, A.company
from CONVENTION A
where A.ssn in ( 'B2B2B2','XYZZY','E5E5E5' );

Query Output, in example 3/3

Note

• This isn’t actually a real join between tables: note that RESTAURANT isn’t even in the query. I just wanted to point out that this is the same in that you can use when adding a manual value filter to a query.

= ANY

SQL Query

select A.name_lf, A.company
from CONVENTION A
where A.ssn = any ( select B.social from RESTAURANT B );

Query Output

Note

This is exactly like in, only you type = any instead. All 3 examples from in apply.

INTERSECT

SQL Query, intersect example 1/2

select A.ssn
from CONVENTION A
intersect
select B.social
from RESTAURANT B;

Query Output, intersect example 1/2

Note

As with minus and union, we can’t output columns that weren’t in our “match criteria.”

A single column of output might suffice if our business problem is a simple “dummy check” list of “all values that appear in both tables.”

SQL Query, intersect example 2/2

select A.ph, A.em
from CONVENTION A
intersect
select B.phone, B.email
from RESTAURANT B;

Query Output, intersect example 2/2

EXISTS

SQL Query

SELECT A.name_lf, A.company
  FROM CONVENTION A
WHERE EXISTS (
  SELECT NULL
    FROM RESTAURANT B
  WHERE A.ph = B.phone and A.em = B.email
);

Query Output

Note

• exists is better than in when your “matching criteria” involve multiple columns.
• Like in, exists doesn’t let you see, or further filter upon, details from B.

INNER JOIN

SQL Query

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
inner join RESTAURANT B on A.ph = B.phone and A.em = B.email;

Query Output

=

The simple = approach to joining tables is extraordinarily widespread and often taught first in tutorials to joining tables with SQL.

It supports “further filters” – just include them as additional and and or clauses beyond the where clause responsible for the “matching” operation.

If the rest of your filters have or in them, be sure to use parentheses to ensure that you don’t accidentally make your “join filter(s)” optional.

Avoiding such concerns is one reason I slightly prefer inner join to the “=” approach.

I almost always use explicit inner join commands when I need to experiment to determine what query I want to write. It helps me avoid careless backspacing errors while I play.

SQL Query

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A, RESTAURANT B
where A.ph = B.phone
and A.em = B.email;

Query Output

Join Type: “In A Or B, But Not Both”

For this join type, we’d expect 6 rows of output (Anush+Benita+Cathy1+Cathy2+Grace+Helen or Anush+Benita+Frances1+Frances2+Grace+Helen, depending on the “matching criteria” we choose).

There aren’t any SQL commands dedicated to this join type, but take a close look at the Venn diagram representing it. Can you see the “bite out of the middle” of what’s otherwise an “In A, B, Or Both” join? Your task is to write code that takes that “bite out of the middle.”

(Side note: it is very difficult to make this Venn diagram as a sugar cookie that won’t break when bringing treats for your Database 101 class’s final exam. Making it in SQL is much easier!)

Choose your own adventure

“Cut out the middle” of your overlapping circles by designing a query according to this architecture:

1. Build an “In A, B, Or Both” query using your favorite approach and think of it as your new A.
2. Build an “In Both A And B” query using your favorite approach and think of it as your new B.
3. Stitch together your new A and your new B using your favorite “In A But Not In B” approach.

FULL OUTER JOIN ... A.... IS NULL OR B.... IS NULL

This approach supports “further filters” – just include them as additional and and or clauses beyond the where clause(s) responsible for the “matching” operation.

Be sure to surround the entire ... or ... filters responsible that “take a bite out of the middle” in their own set of parentheses, to avoid unexpected behavior.

If the rest of your filters have or in them, use parentheses around them, too, to ensure that you don’t accidentally make your “bite out of the middle” filter optional.

SQL Query

select A.name_lf, A.company, B.name_fl, B.fav_food
from CONVENTION A
full outer join RESTAURANT B on A.ph = B.phone and A.em = B.email
where 
	(
	A.ph is null
	and A.em is null
	)
or
	(
	B.phone is null
	and B.email is null
	);

Query Output

More Resources

If you liked this post, other links that may interest you are:

• SQL Tutorials by Mode Analytics
• All about SQL JOINs by Helen Anderson
• How to Make the Leap from Excel to SQL by Benn Stancil
• 4 Ways to Join Only The First Row in SQL by Periscope Data
• More dangerous subleties of JOINs in SQL by Alex Petralia
• JOINs in SQL, Python, and R by Sadavath Sharma
• Python and SQL Overlap by David Wallace
• A similar blog post about Salesforce’s “SOQL” query language by Jeff Douglas