Tous les jointures SQL dont vous aurez besoin

01 Oct 2018 🔖 bases de données sql
💬 FR

Ben … peut-être pas tous. Mais avec ce cheatsheet, j’ai réussi cent pour cent des questions d’un examen universitaire sur les bases de données, donc j’imagine qu’il vaut quelque chose.

C’était un projet de trouver ce cheatsheet, parce que je n’avais pas même enregistré avec le mot “jointure” au nom du fichier. Je l’avais nommé “opérations ensemblistes en SQL”. Et c’est vraiment ce dont il s’agit. Il n’importe pas si l’on se sert du SQL, du VLOOKUP en Excel, du Python, ou d’un stylo et du papier pour comparer deux ensembles d’enregistrements. Combiner un ensemble de choses avec un autre est un problème mathématique. Mais c’est le calcul plaisant, pas le calcul difficile. On fait des diagrammes de Venn; c’es le genre de calcul qui vous permet de dessiner comme si vous étiez à l’école élémentaire.

Donné deux ensembles d’enregistrements (chaque “enregistrement” représente quelque chose dans la vie : une personne, une facture d’achat, une nation) éxistent dans un seul système de gestion de base de données relationnelle (SGBDR – on dit aussi tout simplement “base de données”), SQL est très utile. Tous ces systèmes offrent la possibilité d’extraire vos données à l’aide du langage de programmation SQL.

Je travaille principalement avec des bases de données Oracle, donc je peux composer la version de SQL “Oracle” plus vite que les autres et m’en servira pour les exemples de cet article. Vous pouvez exécuter du SQL Oracle en ligne avec un compte gratuit avec l’outil Live SQL.

Pour traduire les exemples vers une autre version du langage SQL, il va falloir peut-être Googler les mots clés à côté de votre système préféré. Par exemple, j’ai constaté que full outer join ne marche pas avec les SGBD MySQL. Donc j’ai recherché “‘full outer join’ mysql” et j’ai découvert qu’une telle commande n’y est pas compris. Il faut combiner d’autres opérations habilement.

Les données d’exemple

Contenu du table “A” (CONGRES)

CONGRES est une liste des participants à un congrès et de leurs lieux de travail.

(Oui, c’est un congrès très indiscret, demandant à ses participants un numéro d’identification national – le “ssn”.)

Contenu du table “B” (RESTAURANT)

RESTAURANT est une liste de clients habituels chez un restaurant, avec leur cuisine préféré et leur âge.

(Oui, c’est un restaurant très curieux qui demande à ses clients habituels un numéro d’identification national – le “social”.)

• Notez que l’adresse mail de Cathy Combs commence avec “ccc” et non “333”.
• Notez qu’il y a un erreur typographique dans le numéro d’identification national de Frances Fulvia.

Code pour créer les tables et inserer les données (“LDD” & “LMD”)

Voici du code pour créer les tables CONGRES et RESTAURANT dans une base de données Oracle et d’y enregistrer les données d’exemple, si vous voulez l’essayer.

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

Qui correspond à qui dans les données

Disons que nous sommes dans une grande entreprise. Une telle grande entreprise que l’on gère le congrès et que l’on possède le restaurant. On va chercher des correspondances entre les deux listes de clients.

A part de ça … je n’ai plus d’histoires pour expliquer les enquêtes qui vont suivre. Je créé les données et les enquêtes pour illustrer des idées logiques. Elles n’ont rien à voir avec la réalité.

Mais revenons à la combinaison de nos listes:

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 CONGRES 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 CONGRES
   • “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 CONGRES
   • “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 CONGRES).

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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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 prn_nom and cuis_pref details for anyone younger than 50.

select A.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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 prn_nom and cuis_pref details for anyone 50 or younger.

select A.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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.nom_prn, A.company
from CONGRES 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.nom_prn, A.company
from CONGRES 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.nom_prn, A.company
from CONGRES 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.nom_prn, A.company
from CONGRES 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 CONGRES 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 CONGRES 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.nom_prn, A.company
  FROM CONGRES 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.nom_prn, A.company
from CONGRES 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 CONGRES 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 CONGRES 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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES A
left outer join RESTAURANT B on A.ssn = B.social
union
select A.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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.nom_prn, A.company
from CONGRES 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.nom_prn, A.company
from CONGRES 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.nom_prn, A.company
from CONGRES 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.nom_prn, A.company
from CONGRES 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 CONGRES 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 CONGRES A
intersect
select B.phone, B.email
from RESTAURANT B;

Query Output, intersect example 2/2

EXISTS

SQL Query

SELECT A.nom_prn, A.company
  FROM CONGRES 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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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.nom_prn, A.company, B.prn_nom, B.cuis_pref
from CONGRES 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
• 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
• JOINs in SQL, Python, and R by Sadavath Sharma
• Python and SQL Overlap by David Wallace