Bases de données

SQL : interrogation de la base de données

Nadime Francis

Université Gustave Eiffel

LIGM - 4B130 Copernic
[email protected]

1 / 23
Qu’est-ce que SQL ?

SQL : Structured Query Language ; langage d’interrogation structuré

Langage déclaratif inspiré du modèle relationnel de Codd (1970)

Langage normalisé mais les implémentations diffèrent légèrement

Ex : Oracle, MySQL, PostgreSQL, Access, ...

Permet de consulter, créer et mettre à jour une base de données

Contient deux sous-langages :
LDD : Langage de définition de données
Définition des structures relationnelles et de leurs contraintes
LMD : Langage de manipulation de données
Interrogation des données, insertions, suppressions, mise à jour

Remarque : l’implémentation de référence pour ce cours est PostgreSQL

2 / 23
Interrogation de la base de données

Requête SELECT :
Produit une table temporaire (la réponse) à partir d’une ou plusieurs
tables de la base de données
Spécifie les lignes (sélection) et colonnes (projection) à extraire

Ex : extraction des noms et prénoms des réalisateurs nés après 1950

→ sélection des lignes rouges et projection sur les colonnes bleues
realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
nom prenom
2 Tarantino Quentin 1963
Tarantino Quentin
3 Carpenter John 1948 =⇒
Cameron James
4 Woo John 1946
Fincher David
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

3 / 23
SELECT : choix des colonnes de sortie

Clause SELECT : choix des colonnes à retourner

L’opérateur ∗ permet de retourner toutes les colonnes
Clause FROM : choix des tables d’où extraire les colonnes

Ex :
SELECT nom,prenom FROM realisateur;

realisateur
idDir nom prenom naissance nom prenom
1 Scott Ridley 1943 Scott Ridley
2 Tarantino Quentin 1963 Tarantino Quentin
3 Carpenter John 1948 =⇒ Carpenter John
4 Woo John 1946 Woo John
5 Cameron James 1954 Cameron James
6 Fincher David 1962 Fincher David
7 Winner Michael 1935 Winner Michael

4 / 23
SELECT : choix des colonnes de sortie

Clause SELECT : choix des colonnes à retourner

L’opérateur ∗ permet de retourner toutes les colonnes
Clause FROM : choix des tables d’où extraire les colonnes

Ex :
SELECT ∗ FROM realisateur;

realisateur
idDir nom prenom naissance idDir nom prenom naissance
1 Scott Ridley 1943 1 Scott Ridley 1943
2 Tarantino Quentin 1963 2 Tarantino Quentin 1963
3 Carpenter John 1948 =⇒ 3 Carpenter John 1948
4 Woo John 1946 4 Woo John 1946
5 Cameron James 1954 5 Cameron James 1954
6 Fincher David 1962 6 Fincher David 1962
7 Winner Michael 1935 7 Winner Michael 1935

4 / 23
WHERE : choix des lignes de sortie

Clause WHERE : choix des lignes à retourner

Spécifie une condition que doivent respecter les lignes de la réponse
Utilisé dans une requête de la forme SELECT ... FROM ... WHERE ...

Ex :
SELECT ∗ FROM realisateur
WHERE prenom = 'John';
-- réalisateurs dont le prénom est John

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
2 Tarantino Quentin 1963 idDir nom prenom naissance
3 Carpenter John 1948 =⇒ 3 Carpenter John 1948
4 Woo John 1946 4 Woo John 1946
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

5 / 23
WHERE : choix des lignes de sortie

Clause WHERE : choix des lignes à retourner

Spécifie une condition que doivent respecter les lignes de la réponse
Utilisé dans une requête de la forme SELECT ... FROM ... WHERE ...

Ex :
SELECT ∗ FROM realisateur
WHERE prenom = 'John' AND nom = 'Carpenter';
-- réalisateurs dont le prénom est John et le nom est Carpenter

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
2 Tarantino Quentin 1963
idDir nom prenom naissance
3 Carpenter John 1948 =⇒
3 Carpenter John 1948
4 Woo John 1946
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

5 / 23
WHERE : choix des lignes de sortie

Clause WHERE : choix des lignes à retourner

Spécifie une condition que doivent respecter les lignes de la réponse
Utilisé dans une requête de la forme SELECT ... FROM ... WHERE ...

Ex :
SELECT ∗ FROM realisateur
WHERE prenom = 'John' OR nom = 'Fincher';
-- réalisateurs dont soit le prénom est John, soit le nom est Fincher (soit les deux)

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
idDir nom prenom naissance
2 Tarantino Quentin 1963
3 Carpenter John 1948
3 Carpenter John 1948 =⇒
4 Woo John 1946
4 Woo John 1946
6 Fincher David 1962
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

5 / 23
WHERE : choix des lignes de sortie

Clause WHERE : choix des lignes à retourner

Spécifie une condition que doivent respecter les lignes de la réponse
Utilisé dans une requête de la forme SELECT ... FROM ... WHERE ...

Ex :
SELECT ∗ FROM realisateur
WHERE prenom = 'John' AND nom = 'Fincher';
-- réalisateurs dont le prénom est John et le nom est Fincher
-- si aucune ligne ne vérifie la conditon, la réponse est une table vide (0 ligne)

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
2 Tarantino Quentin 1963
3 Carpenter John 1948 =⇒ idDir nom prenom naissance
4 Woo John 1946
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

5 / 23
SELECT FROM WHERE : projection et sélection

Requêtes de la forme SELECT ... FROM ... WHERE ...

Spécifie les lignes et les colonnes à conserver
Forme la plus courante de requêtes SQL
Autorise le test de condition sur un attribut qui n’est pas conservé

Ex :
SELECT nom,naissance FROM realisateur
WHERE naissance >= 1950;
-- nom et date de naissance des réalisateurs nés après 1950

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
nom naissance
2 Tarantino Quentin 1963
Tarantino 1963
3 Carpenter John 1948 =⇒
Cameron 1954
4 Woo John 1946
Fincher 1962
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

6 / 23
SELECT FROM WHERE : projection et sélection

Requêtes de la forme SELECT ... FROM ... WHERE ...

Spécifie les lignes et les colonnes à conserver
Forme la plus courante de requêtes SQL
Autorise le test de condition sur un attribut qui n’est pas conservé

Ex :
SELECT nom FROM realisateur
WHERE prenom = 'John';
-- nom des réalisateurs dont le prénom est John, valide même sans projeter prenom

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
2 Tarantino Quentin 1963 nom
3 Carpenter John 1948 =⇒ Carpenter
4 Woo John 1946 Woo
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

6 / 23
Quelques conditions utiles
SELECT ∗ FROM realisateur
Égalité et différence : =, <> WHERE prenom = 'John';

Comparaisons arithmétiques : SELECT ∗ FROM realisateur

WHERE naissance <= 1950;
<, <=, >=, >

Encadrement : BETWEEN SELECT ∗ FROM realisateur

WHERE nom BETWEEN 'C' AND 'L';
(d’entiers, de dates ou de chaines)

Filtres de chaînes : LIKE SELECT ∗ FROM realisateur

un caractère manquant : _
WHERE prenom LIKE 'John%';
-- sélectionne John, Johnny, Johnatan, ...
0, un ou plus d’un caractère : %

SELECT ∗ FROM realisateur

WHERE prenom NOT IN ('John','James');
Appartenance : IN, NOT IN
-- réalisateurs ne s'appelant ni John ni James

Et leurs combinaisons booléennes avec AND, OR, NOT

7 / 23
NULL : valeurs manquantes

NULL représente une valeur manquante ou inconnue

Sous PostgreSQL, les NULL sont représentés par des cases vides
Attention, NULL n’est ni zéro, ni la chaine vide, ni un espace blanc...

Les comparaisons et opérations avec NULL renvoient NULL

NULL + 1 vaut NULL, NULL LIKE 'John' renvoie NULL, etc.

IS NULL et IS NOT NULL servent à tester si une valeur est NULL

(a = NULL) et (a <> NULL) ne fonctionnent pas !

Les combinaisons booléennes avec NULL suivent les règles suivantes :

A B A AND B A B A OR B
True True True True True True
A NOT A
True False False True False True
True False
False False False False False False
False True
True Null Null True Null True
Null Null
False Null False False Null Null
Null Null Null Null Null Null

8 / 23
Exemple : NULL et tests
mouton
nom couleurPeau couleurLaine
Dolly blanc blanc
Sheepy
Chris blanc noir
Buttermilk noir
Lambo noir noir

9 / 23
Exemple : NULL et tests
mouton
nom couleurPeau couleurLaine
Dolly blanc blanc
Sheepy
Chris blanc noir
Buttermilk noir
Lambo noir noir

SELECT nom FROM mouton

WHERE couleurLaine = couleurPeau;
-- moutons dont la laine et la peau sont assorties

9 / 23
Exemple : NULL et tests
mouton
nom couleurPeau couleurLaine
Dolly blanc blanc
Sheepy
Chris blanc noir
Buttermilk noir
Lambo noir noir

SELECT nom FROM mouton nom

WHERE couleurLaine = couleurPeau; Dolly
-- moutons dont la laine et la peau sont assorties Lambo

9 / 23
Exemple : NULL et tests
mouton
nom couleurPeau couleurLaine
Dolly blanc blanc
Sheepy
Chris blanc noir
Buttermilk noir
Lambo noir noir

SELECT nom FROM mouton nom

WHERE couleurLaine = couleurPeau; Dolly
-- moutons dont la laine et la peau sont assorties Lambo

SELECT nom FROM mouton

WHERE couleurLaine = NULL;
-- que calcule cette requête ?

9 / 23
Exemple : NULL et tests
mouton
nom couleurPeau couleurLaine
Dolly blanc blanc
Sheepy
Chris blanc noir
Buttermilk noir
Lambo noir noir

SELECT nom FROM mouton nom

WHERE couleurLaine = couleurPeau; Dolly
-- moutons dont la laine et la peau sont assorties Lambo

SELECT nom FROM mouton

WHERE couleurLaine = NULL; nom
-- que calcule cette requête ?

C’est la seule réponse cohérente avec la requête précédente !

9 / 23
Exemple : NULL et tests
mouton
nom couleurPeau couleurLaine
Dolly blanc blanc
Sheepy
Chris blanc noir
Buttermilk noir
Lambo noir noir

SELECT nom FROM mouton nom

WHERE couleurLaine = couleurPeau; Dolly
-- moutons dont la laine et la peau sont assorties Lambo

SELECT nom FROM mouton

WHERE couleurLaine = NULL; nom
-- que calcule cette requête ?

C’est la seule réponse cohérente avec la requête précédente !

SELECT nom FROM mouton

WHERE couleurLaine IS NULL;
-- correction : moutons dont la couleur de laine est inconnue
9 / 23
Exemple : NULL et tests
mouton
nom couleurPeau couleurLaine
Dolly blanc blanc
Sheepy
Chris blanc noir
Buttermilk noir
Lambo noir noir

SELECT nom FROM mouton nom

WHERE couleurLaine = couleurPeau; Dolly
-- moutons dont la laine et la peau sont assorties Lambo

SELECT nom FROM mouton

WHERE couleurLaine = NULL; nom
-- que calcule cette requête ?

C’est la seule réponse cohérente avec la requête précédente !

SELECT nom FROM mouton nom

WHERE couleurLaine IS NULL; Sheepy
-- correction : moutons dont la couleur de laine est inconnue Buttermilk
9 / 23
Exemple : NULL et partitions
produit
ref libelle couleur prix
45 bureau noir 110
73 classeur 9.99
13 classeur rouge 12.99
29 étagère merisier 45.99
14 fauteuil rouge 99.95
27 moniteur 139.99
15 clavier noir 29.99

SELECT ∗ FROM produit WHERE couleur = 'rouge';

SELECT ∗ FROM produit WHERE couleur <> 'rouge';

SELECT ∗ FROM produit WHERE couleur IS NULL;

SELECT ∗ FROM produit WHERE couleur IS NOT NULL;

10 / 23
Exemple : NULL et partitions
produit
ref libelle couleur prix
45 bureau noir 110
73 classeur 9.99
13 classeur rouge 12.99
29 étagère merisier 45.99
14 fauteuil rouge 99.95
27 moniteur 139.99
15 clavier noir 29.99

I SELECT ∗ FROM produit WHERE couleur = 'rouge';

SELECT ∗ FROM produit WHERE couleur <> 'rouge';

SELECT ∗ FROM produit WHERE couleur IS NULL;

SELECT ∗ FROM produit WHERE couleur IS NOT NULL;

10 / 23
Exemple : NULL et partitions
produit
ref libelle couleur prix
45 bureau noir 110
73 classeur 9.99
13 classeur rouge 12.99
29 étagère merisier 45.99
14 fauteuil rouge 99.95
27 moniteur 139.99
15 clavier noir 29.99

SELECT ∗ FROM produit WHERE couleur = 'rouge';

I SELECT ∗ FROM produit WHERE couleur <> 'rouge';

SELECT ∗ FROM produit WHERE couleur IS NULL;

SELECT ∗ FROM produit WHERE couleur IS NOT NULL;

10 / 23
Exemple : NULL et partitions
produit
ref libelle couleur prix
45 bureau noir 110
73 classeur 9.99
13 classeur rouge 12.99
29 étagère merisier 45.99
14 fauteuil rouge 99.95
27 moniteur 139.99
15 clavier noir 29.99

SELECT ∗ FROM produit WHERE couleur = 'rouge';

SELECT ∗ FROM produit WHERE couleur <> 'rouge';

I SELECT ∗ FROM produit WHERE couleur IS NULL;

SELECT ∗ FROM produit WHERE couleur IS NOT NULL;

10 / 23
Exemple : NULL et partitions
produit
ref libelle couleur prix
45 bureau noir 110
73 classeur 9.99
13 classeur rouge 12.99
29 étagère merisier 45.99
14 fauteuil rouge 99.95
27 moniteur 139.99
15 clavier noir 29.99

SELECT ∗ FROM produit WHERE couleur = 'rouge';

SELECT ∗ FROM produit WHERE couleur <> 'rouge';

SELECT ∗ FROM produit WHERE couleur IS NULL;

I SELECT ∗ FROM produit WHERE couleur IS NOT NULL;

10 / 23
DISTINCT : suppression des doublons

SELECT DISTINCT ne conserve que les lignes différentes

La comparaison est faite sur la ligne en entier

Ex :
SELECT DISTINCT nom,prenom FROM realisateur;

realisateur
idDir nom prenom naissance
nom prenom
1 Scott Ridley 1943
Scott Ridley
2 Tarantino Quentin 1963
Tarantino Quentin
3 Carpenter John 1948
Carpenter John
4 Woo John 1946
=⇒ Woo John
5 Cameron James 1954
Cameron James
6 Fincher David 1962
Fincher David
7 Winner Michael 1935
Winner Michael
8 Lynch David 1946
Lynch David
9 McQueen Steve 1930
McQueen Steve
10 McQueen Steve 1969

11 / 23
DISTINCT : suppression des doublons

SELECT DISTINCT ne conserve que les lignes différentes

La comparaison est faite sur la ligne en entier

Ex :
SELECT DISTINCT prenom FROM realisateur;

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943 prenom
2 Tarantino Quentin 1963 Ridley
3 Carpenter John 1948 Quentin
4 Woo John 1946 John
=⇒
5 Cameron James 1954 James
6 Fincher David 1962 David
7 Winner Michael 1935 Michael
8 Lynch David 1946 Steve
9 McQueen Steve 1930
10 McQueen Steve 1969

11 / 23
SELECT avec opérations sur les valeurs

Il est possible d’effectuer des opérations sur les valeurs

Ex : arithmétique sur les entiers, concaténation de chaînes...
Le résultat peut servir dans une condition ou être renvoyé
S’il est renvoyé, on peut nommer la colonne correspondante avec AS

Ex :
SELECT nom,prenom FROM realisateur
WHERE (2018 - naissance) <= 60;

realisateur
idDir nom prenom naissance
1 Scott Ridley 1943
2 Tarantino Quentin 1963
nom prenom
3 Carpenter John 1948 =⇒
Tarantino Quentin
4 Woo John 1946
Fincher David
5 Cameron James 1954
6 Fincher David 1962
7 Winner Michael 1935

12 / 23
SELECT avec opérations sur les valeurs

Il est possible d’effectuer des opérations sur les valeurs

Ex : arithmétique sur les entiers, concaténation de chaînes...
Le résultat peut servir dans une condition ou être renvoyé
S’il est renvoyé, on peut nommer la colonne correspondante avec AS

Ex :
SELECT prenom||' '||nom AS nomComplet, 2018 - naissance AS age
FROM realisateur;

realisateur
idDir nom prenom naissance nomComplet age
1 Scott Ridley 1943 Ridley Scott 75
2 Tarantino Quentin 1963 Quentin Tarantino 55
3 Carpenter John 1948 =⇒ John Carpenter 70
4 Woo John 1946 John Woo 72
5 Cameron James 1954 James Cameron 64
6 Fincher David 1962 David Fincher 54
7 Winner Michael 1935 Michael Winner 83

12 / 23
SELECT sur plusieurs tables
Il est possible d’indiquer plusieurs tables dans la clause FROM
La requête est effectuée sur le produit cartésien des tables en entrée
Attention, ce n’est pas, en général, le résultat désiré (voir exemple)
Ex :
film realisateur
idF titre annee idDir idDir nom prenom naissance
1 Alien 1979 1 1 Scott Ridley 1943
2 Reservoir Dogs 1992 2 2 Tarantino Quentin 1963
4 Volte-face 1997 4 3 Carpenter John 1948
5 Pulp Fiction 1995 2 4 Woo John 1946

SELECT titre, annee, nom, prenom FROM film, realisateur;

titre annee nom prenom

Alien 1979 Scott Ridley
Alien 1979 Tarantino Quentin
Alien 1979 Carpenter John
Alien 1979 Woo John
Reservoir Dogs 1992 Scott Ridley
Reservoir Dogs 1992 Tarantino Quentin
Reservoir Dogs 1992 Carpenter John
Reservoir Dogs 1992 Woo John
Volte-face 1997 Scott Ridley
Volte-face 1997 Tarantino Quentin
. . . .
. . . .
. . . .
13 / 23
Jointure : un produit cartésien filtré
Sélection des lignes pertinentes dans une requête sur plusieurs tables
Deux syntaxes équivalentes :
SELECT colonnes FROM tab1, tab2 WHERE condition
SELECT colonnes FROM tab1 JOIN tab2 ON condition
Notation pointée table.colonne si deux colonnes ont le même nom

Ex :
film realisateur
idF titre annee idDir idDir nom prenom naissance
1 Alien 1979 1 1 Scott Ridley 1943
2 Reservoir Dogs 1992 2 2 Tarantino Quentin 1963
4 Volte-face 1997 4 3 Carpenter John 1948
5 Pulp Fiction 1995 2 4 Woo John 1946

SELECT titre, annee, nom, prenom FROM film, realisateur

WHERE film.idDir = realisateur.idDir;

titre annee nom prenom

Alien 1979 Scott Ridley
Reservoir Dogs 1992 Tarantino Quentin
Volte-face 1997 Woo John
Pulp Fiction 1995 Tarantino Quentin

14 / 23
Jointure : un produit cartésien filtré
Sélection des lignes pertinentes dans une requête sur plusieurs tables
Deux syntaxes équivalentes :
SELECT colonnes FROM tab1, tab2 WHERE condition
SELECT colonnes FROM tab1 JOIN tab2 ON condition
Notation pointée table.colonne si deux colonnes ont le même nom

Ex :
film realisateur
idF titre annee idDir idDir nom prenom naissance
1 Alien 1979 1 1 Scott Ridley 1943
2 Reservoir Dogs 1992 2 2 Tarantino Quentin 1963
4 Volte-face 1997 4 3 Carpenter John 1948
5 Pulp Fiction 1995 2 4 Woo John 1946

SELECT titre, annee, nom, prenom FROM film JOIN realisateur

ON film.idDir = realisateur.idDir;

titre annee nom prenom

Alien 1979 Scott Ridley
Reservoir Dogs 1992 Tarantino Quentin
Volte-face 1997 Woo John
Pulp Fiction 1995 Tarantino Quentin

14 / 23
Jointure naturelle

NATURAL JOIN : jointure avec égalité des attributs de même nom

Une seule occurrence de chaque colonne est conservée
Forme la plus fréquente de jointure

Ex :
film realisateur
idF titre annee idDir idDir nom prenom naissance
1 Alien 1979 1 1 Scott Ridley 1943
2 Reservoir Dogs 1992 2 2 Tarantino Quentin 1963
4 Volte-face 1997 4 3 Carpenter John 1948
5 Pulp Fiction 1995 2 4 Woo John 1946

SELECT ∗ FROM film NATURAL JOIN realisateur;

idF titre annee idDir nom prenom naissance

1 Alien 1979 1 Scott Ridley 1943
2 Reservoir Dogs 1992 2 Tarantino Quentin 1963
4 Volte-face 1997 4 Woo John 1946
5 Pulp Fiction 1995 2 Tarantino Quentin 1963

15 / 23
Auto-jointure d’une table
Auto-jointure : jointure d’une table avec elle-même
Attention à donner un nom différent à chaque occurence de la table
Ex : film
idF titre annee idDir
1 Alien 1979 1
2 Reservoir Dogs 1992 2
4 Volte-face 1997 4
5 Pulp Fiction 1995 2
6 Terminator 1984 5
8 The Game 1997 6
9 The Game 1970 7

SELECT f1.titre AS titre1, f2.titre AS titre2

FROM film AS f1, film AS f2
WHERE f1.annee = f2.annee
AND f1.idF <> f2.idF;
-- liste des paires de films différents qui sont sortis la même année

16 / 23
Auto-jointure d’une table
Auto-jointure : jointure d’une table avec elle-même
Attention à donner un nom différent à chaque occurence de la table
Ex : film
idF titre annee idDir
1 Alien 1979 1
2 Reservoir Dogs 1992 2
4 Volte-face 1997 4
5 Pulp Fiction 1995 2
6 Terminator 1984 5
8 The Game 1997 6
9 The Game 1970 7

SELECT f1.titre AS titre1, f2.titre AS titre2

FROM film AS f1, film AS f2
WHERE f1.annee = f2.annee
AND f1.idF <> f2.idF;
-- liste des paires de films différents qui sont sortis la même année

titre1 titre2
Volte-face The Game
The Game Volte-face

16 / 23
Auto-jointure d’une table
Auto-jointure : jointure d’une table avec elle-même
Attention à donner un nom différent à chaque occurence de la table
Ex : film
idF titre annee idDir
1 Alien 1979 1
2 Reservoir Dogs 1992 2
4 Volte-face 1997 4
5 Pulp Fiction 1995 2
6 Terminator 1984 5
8 The Game 1997 6
9 The Game 1970 7

SELECT f1.titre AS titre1, f2.titre AS titre2

FROM film AS f1, film AS f2
WHERE f1.annee = f2.annee
AND f1.idF < f2.idF;
-- liste des paires de films différents qui sont sortis la même année
-- une occurence par paire

titre1 titre2
Volte-face The Game

16 / 23
Exercice : jointures
On considère le schéma relationnel :

produit(ref, libelle, couleur, prix)

usine(id, nom, adresse, ville)
fabrique(id, ref, cout)
FK : ref dans fabrique fait référence à ref dans produit
FK : id dans fabrique fait référence à id dans usine

Proposer des requêtes SQL calculant les informations suivantes :

1 Les usines qui fabriquent des bureaux

2 Les usines qui fabriquent des produits à perte

3 Les produits fabriqués à Paris ou à Lyon

4 Les libellés de produits qui existent à la fois en rouge et en bleu

5 Les villes où sont fabriqués à la fois des bureaux et des marteaux-piqueurs

17 / 23
Jointure externe

La jointure (tab1 JOIN tab2 ON condition) ne contient pas les lignes de

tab1 qui n’ont pas de correspondance dans tab2 (et inversement)
La jointure externe conserve et complète ces lignes avec NULL
(tab1 LEFT OUTER JOIN tab2) conserve les lignes de tab1
(tab1 RIGHT OUTER JOIN tab2) conserve les lignes de tab2
(tab1 FULL OUTER JOIN tab2) conserve les lignes des deux tables

Le mot clef OUTER est optionnel

Ex :
c s c JOIN s ON symbole = icone
nom symbole icone sens nom symbole icone sens
coeur ♥ ♥ amour
coeur ♥ ♥ amour coeur ♥ ♥ vie
pique ♠ ♥ vie trèfle ♣ ♣ chance
trèfle ♣ ♣ chance
carreau ♦ F magie

18 / 23
Jointure externe

La jointure (tab1 JOIN tab2 ON condition) ne contient pas les lignes de

tab1 qui n’ont pas de correspondance dans tab2 (et inversement)
La jointure externe conserve et complète ces lignes avec NULL
(tab1 LEFT OUTER JOIN tab2) conserve les lignes de tab1
(tab1 RIGHT OUTER JOIN tab2) conserve les lignes de tab2
(tab1 FULL OUTER JOIN tab2) conserve les lignes des deux tables

Le mot clef OUTER est optionnel

Ex :
c s c LEFT JOIN s ON symbole = icone
nom symbole icone sens nom symbole icone sens
coeur ♥ ♥ amour
coeur ♥ ♥ amour coeur ♥ ♥ vie
pique ♠ ♥ vie trèfle ♣ ♣ chance
trèfle ♣ ♣ chance pique ♠
carreau ♦ F magie carreau ♦

18 / 23
Jointure externe

La jointure (tab1 JOIN tab2 ON condition) ne contient pas les lignes de

tab1 qui n’ont pas de correspondance dans tab2 (et inversement)
La jointure externe conserve et complète ces lignes avec NULL
(tab1 LEFT OUTER JOIN tab2) conserve les lignes de tab1
(tab1 RIGHT OUTER JOIN tab2) conserve les lignes de tab2
(tab1 FULL OUTER JOIN tab2) conserve les lignes des deux tables

Le mot clef OUTER est optionnel

Ex :
c s c RIGHT JOIN s ON symbole = icone
nom symbole icone sens nom symbole icone sens
coeur ♥ ♥ amour
coeur ♥ ♥ amour coeur ♥ ♥ vie
pique ♠ ♥ vie trèfle ♣ ♣ chance
trèfle ♣ ♣ chance F magie
carreau ♦ F magie

18 / 23
Jointure externe

La jointure (tab1 JOIN tab2 ON condition) ne contient pas les lignes de

tab1 qui n’ont pas de correspondance dans tab2 (et inversement)
La jointure externe conserve et complète ces lignes avec NULL
(tab1 LEFT OUTER JOIN tab2) conserve les lignes de tab1
(tab1 RIGHT OUTER JOIN tab2) conserve les lignes de tab2
(tab1 FULL OUTER JOIN tab2) conserve les lignes des deux tables

Le mot clef OUTER est optionnel

Ex :
c s c FULL JOIN s ON symbole = icone
nom symbole icone sens nom symbole icone sens
coeur ♥ ♥ amour
coeur ♥ ♥ amour coeur ♥ ♥ vie
pique ♠ ♥ vie trèfle ♣ ♣ chance
trèfle ♣ ♣ chance pique ♠
carreau ♦ F magie carreau ♦
F magie

18 / 23
Exemple : jointure externe
etudiant
num nom prenom stage
1 Turing Alan code intitule numEtud
2 Einstein Albert 27 Anneaux exotiques 3
3 Emmy Noether 18 Autour de l’eau 4
4 Lavoisier Antoine 13 Cryptanalyse appliquée 1
5 Clément Ader

SELECT num, nom, prenom, intitule

FROM etudiant LEFT JOIN stage
ON num = numEtud;
-- liste des étudiants avec leur sujet de stage (pour ceux qui en ont un)

num nom prenom intitule

1 Turing Alan Cryptanalyse appliquée
2 Einstein Albert
3 Emmy Noether Anneaux exotiques
4 Lavoisier Antoine Autour de l’eau
5 Clément Ader

Autres exemples :
Relevé de notes incluant les matières qui n’ont pas été évaluées
Planning incluant les créneaux horaires qui ne sont pas occupés
19 / 23
Opérations ensemblistes
Opérations ensemblistes pour combiner deux requêtes Q1 et Q2 :
Q1 UNION Q2 : lignes renvoyées soit par Q1 soit par Q2
Q1 INTERSECT Q2 : lignes renvoyées à la fois par Q1 et par Q2
Q1 EXCEPT Q2 : lignes renvoyées par Q1 mais pas par Q2

Remarques :
Q1 et Q2 doivent être compatibles
Q1 et Q2 doivent renvoyer autant de colonnes
Les types des colonnes correspondantes doivent être compatibles
Les opérations ensemblistes suppriment les doublons

Ex :
SELECT libelle FROM produit
WHERE couleur = 'noir'
INTERSECT
SELECT libelle FROM produit
WHERE couleur = 'rouge';
-- produits existant à la fois en rouge et en noir

20 / 23
Opérations ensemblistes
Opérations ensemblistes pour combiner deux requêtes Q1 et Q2 :
Q1 UNION Q2 : lignes renvoyées soit par Q1 soit par Q2
Q1 INTERSECT Q2 : lignes renvoyées à la fois par Q1 et par Q2
Q1 EXCEPT Q2 : lignes renvoyées par Q1 mais pas par Q2

Remarques :
Q1 et Q2 doivent être compatibles
Q1 et Q2 doivent renvoyer autant de colonnes
Les types des colonnes correspondantes doivent être compatibles
Les opérations ensemblistes suppriment les doublons

Ex :
SELECT num, nom, prenom
FROM etudiant
EXCEPT
SELECT num, nom, prenom
FROM etudiant NATURAL JOIN stage;
-- étudiants qui n'effectuent pas de stage

20 / 23
♣ ALL : opérations ensemblistes avec doublons
Option ALL : variante avec doublons des opérations ensemblistes
La multiplicité (nombre de copies) de chaque ligne du résultat est :
Q1 UNION ALL Q2 : la somme des multiplicitées
Q1 INTERSECT ALL Q2 : le minimum des multiplicités
Q1 EXCEPT ALL Q2 : la différence des multiplicités

Ex : panier1 panier2 bonAchat

produit couleur produit couleur produit
chaise bois chaise bois chaise
chaise bois chaise rouge chaise
table noir chaise bois table
table noir table noir classeur

produit couleur
chaise bois
SELECT ∗ FROM panier1 chaise bois
UNION ALL table noir
table noir
SELECT ∗ FROM panier2 chaise bois
-- le contenu total des deux paniers chaise rouge
chaise bois
table noir
21 / 23
♣ ALL : opérations ensemblistes avec doublons
Option ALL : variante avec doublons des opérations ensemblistes
La multiplicité (nombre de copies) de chaque ligne du résultat est :
Q1 UNION ALL Q2 : la somme des multiplicitées
Q1 INTERSECT ALL Q2 : le minimum des multiplicités
Q1 EXCEPT ALL Q2 : la différence des multiplicités

Ex : panier1 panier2 bonAchat

produit couleur produit couleur produit
chaise bois chaise bois chaise
chaise bois chaise rouge chaise
table noir chaise bois table
table noir table noir classeur

SELECT ∗ FROM panier1 produit couleur

INTERSECT ALL chaise bois
SELECT ∗ FROM panier2 chaise bois
-- les objets en commun entre les deux paniers table noir

21 / 23
♣ ALL : opérations ensemblistes avec doublons
Option ALL : variante avec doublons des opérations ensemblistes
La multiplicité (nombre de copies) de chaque ligne du résultat est :
Q1 UNION ALL Q2 : la somme des multiplicitées
Q1 INTERSECT ALL Q2 : le minimum des multiplicités
Q1 EXCEPT ALL Q2 : la différence des multiplicités

Ex : panier1 panier2 bonAchat

produit couleur produit couleur produit
chaise bois chaise bois chaise
chaise bois chaise rouge chaise
table noir chaise bois table
table noir table noir classeur

SELECT produit FROM panier1 produit

UNION ALL table
SELECT produit FROM panier2 chaise
EXCEPT ALL chaise
SELECT ∗ FROM bonAchat chaise
table
-- les objets restant à payer après déduction du bon d'achat

21 / 23
ORDER BY : trier les résultats d’une requête

Clause ORDER BY : tri des lignes suivant une ou plusieurs colonnes

Tri par ordre croissant (par défaut ou ASC) ou décroissant (DESC)
Ex :
SELECT ∗ FROM film;
-- liste des films
-- pas de garantie sur l'ordre

film
idF titre annee idDir
1 Alien 1979 1
2 Reservoir Dogs 1992 2
4 Volte-face 1997 4
5 Pulp Fiction 1995 2
6 Terminator 1984 5
8 The Game 1997 6
9 The Game 1970 7

22 / 23
ORDER BY : trier les résultats d’une requête

Clause ORDER BY : tri des lignes suivant une ou plusieurs colonnes

Tri par ordre croissant (par défaut ou ASC) ou décroissant (DESC)
Ex :
SELECT ∗ FROM film
ORDER BY annee;
-- liste des films, du plus vieux au plus récent

film
idF titre annee idDir
9 The Game 1970 7
1 Alien 1979 1
6 Terminator 1984 5
2 Reservoir Dogs 1992 2
5 Pulp Fiction 1995 2
4 Volte-face 1997 4
8 The Game 1997 6

22 / 23
ORDER BY : trier les résultats d’une requête

Clause ORDER BY : tri des lignes suivant une ou plusieurs colonnes

Tri par ordre croissant (par défaut ou ASC) ou décroissant (DESC)
Ex :
SELECT ∗ FROM film
ORDER BY annee, titre;
-- liste des films triés par année puis par titre

film
idF titre annee idDir
9 The Game 1970 7
1 Alien 1979 1
6 Terminator 1984 5
2 Reservoir Dogs 1992 2
5 Pulp Fiction 1995 2
8 The Game 1997 6
4 Volte-face 1997 4

22 / 23
ORDER BY : trier les résultats d’une requête

Clause ORDER BY : tri des lignes suivant une ou plusieurs colonnes

Tri par ordre croissant (par défaut ou ASC) ou décroissant (DESC)
Ex :
SELECT ∗ FROM film
ORDER BY annee DESC;
-- liste des films, du plus récent au plus vieux

film
idF titre annee idDir
8 The Game 1997 6
4 Volte-face 1997 4
5 Pulp Fiction 1995 2
2 Reservoir Dogs 1992 2
6 Terminator 1984 5
1 Alien 1979 1
9 The Game 1970 7

22 / 23
LIMIT et OFFSET : choix du nombre de lignes du résultat

Clause LIMIT (n) : limite la taille du résultat à n lignes

Clause OFFSET (m) : ignore les m premières lignes du résultat
LIMIT et OFFSET s’utilisent toujours avec ORDER BY
Sans ORDER BY, aucune garantie sur le résultat
Ex :
SELECT ∗ FROM film
ORDER BY annee DESC;
-- liste des films, du plus récent au plus vieux

film
idF titre annee idDir
8 The Game 1997 6
4 Volte-face 1997 4
5 Pulp Fiction 1995 2
2 Reservoir Dogs 1992 2
6 Terminator 1984 5
1 Alien 1979 1
9 The Game 1970 7
23 / 23
LIMIT et OFFSET : choix du nombre de lignes du résultat

Clause LIMIT (n) : limite la taille du résultat à n lignes

Clause OFFSET (m) : ignore les m premières lignes du résultat
LIMIT et OFFSET s’utilisent toujours avec ORDER BY
Sans ORDER BY, aucune garantie sur le résultat
Ex :
SELECT ∗ FROM film
ORDER BY annee DESC
LIMIT 3;
-- les trois films les plus récents

film
idF titre annee idDir
8 The Game 1997 6
4 Volte-face 1997 4
5 Pulp Fiction 1995 2

23 / 23
LIMIT et OFFSET : choix du nombre de lignes du résultat

Clause LIMIT (n) : limite la taille du résultat à n lignes

Clause OFFSET (m) : ignore les m premières lignes du résultat
LIMIT et OFFSET s’utilisent toujours avec ORDER BY
Sans ORDER BY, aucune garantie sur le résultat
Ex :
SELECT ∗ FROM film
ORDER BY annee DESC
LIMIT 3
OFFSET 2;
-- les troisième, quatrième et cinquième films les plus récents

film
idF titre annee idDir
5 Pulp Fiction 1995 2
2 Reservoir Dogs 1992 2
6 Terminator 1984 5

23 / 23