UNION et vues (VIEW)

Les exemples utilisent la base de données d’exemple de la série Bases du SQL (tables customers, products et orders). Avant vos essais, remettez la base dans son état initial en relançant son script. Les résultats sont de vrais résultats de MySQL 8.0 et MariaDB 10.4 ; là où ils diffèrent, les deux sont indiqués.

UNION ALL – les résultats les uns sous les autres

La recherche du site doit trouver des produits et des clients. UNION réunit deux requêtes sur des tables différentes en un seul résultat :

SELECT 'product' AS type, name AS text FROM products WHERE name LIKE '%er%'
UNION ALL
SELECT 'customer', name FROM customers WHERE name LIKE '%er%'
ORDER BY type, text;

+----------+----------------+
| type     | text           |
+----------+----------------+
| customer | Lucy Turner    |
| customer | Peter Brown    |
| product  | Coffee grinder |
+----------+----------------+

Les règles d’UNION :

• Toutes les parties doivent avoir le même nombre de colonnes. Les types sont unifiés : un nombre et un texte donnent un texte.
• Les noms des colonnes du résultat proviennent de la première partie (c’est pourquoi elle seule a des alias).
• ORDER BY et LIMIT à la fin s’appliquent à l’ensemble du résultat.

UNION et UNION ALL

Quels clients ont acheté du café ou du thé ?

SELECT customer_id FROM orders WHERE product_id = 1
UNION
SELECT customer_id FROM orders WHERE product_id = 2
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
+-------------+

UNION seul supprime les lignes répétées. UNION ALL les conserve :

SELECT customer_id FROM orders WHERE product_id = 1
UNION ALL
SELECT customer_id FROM orders WHERE product_id = 2
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           1 |
|           2 |
|           3 |
|           4 |
+-------------+

La cliente numéro 1 a acheté les deux, c’est pourquoi elle figure deux fois dans la liste. La suppression des doublons coûte du temps (il faut comparer le résultat) : écrivez donc UNION ALL partout où les parties ne se recoupent pas ou où les doublons ne gênent pas. N’utilisez UNION seul que là où vous ne voulez vraiment pas de lignes répétées.

ORDER BY et LIMIT dans les différentes parties

Une partie entre parenthèses peut avoir son propre tri et sa propre limite. Le produit le plus cher et le moins cher dans un seul résultat :

(SELECT 'most expensive' AS what, name, price FROM products ORDER BY price DESC LIMIT 1)
UNION ALL
(SELECT 'cheapest', name, price FROM products ORDER BY price LIMIT 1);

+----------------+----------------+-------+
| what           | name           | price |
+----------------+----------------+-------+
| most expensive | Coffee grinder | 49.00 |
| cheapest       | Cane sugar     |  2.40 |
+----------------+----------------+-------+

Ligne de total – WITH ROLLUP

On ajoute souvent aux rapports une ligne de total général avec UNION ALL. Plus simple : GROUP BY … WITH ROLLUP, qui ajoute lui-même la ligne de total. Dans la colonne de regroupement, la ligne de total contient NULL. COALESCE le remplace par un texte :

SELECT COALESCE(category, 'Total') AS category, SUM(price * stock) AS stock_value
FROM products
GROUP BY category WITH ROLLUP;

+-------------+-------------+
| category    | stock_value |
+-------------+-------------+
| Accessories |      245.00 |
| Drinks      |      643.50 |
| Food        |      240.00 |
| Tableware   |       78.00 |
| Total       |     1206.50 |
+-------------+-------------+

Intersection et différence – INTERSECT et EXCEPT

INTERSECT renvoie les lignes présentes dans les deux résultats, EXCEPT les lignes du premier résultat absentes du second. MariaDB les prend en charge depuis la version 10.3, MySQL seulement depuis la version 8.0.31. Qui a acheté du café et du thé :

SELECT customer_id FROM orders WHERE product_id = 1
INTERSECT
SELECT customer_id FROM orders WHERE product_id = 2;

Résultat en MariaDB :

+-------------+
| customer_id |
+-------------+
|           1 |
+-------------+

Qui a acheté du café, mais pas de thé :

SELECT customer_id FROM orders WHERE product_id = 1
EXCEPT
SELECT customer_id FROM orders WHERE product_id = 2
ORDER BY customer_id;

Résultat en MariaDB :

+-------------+
| customer_id |
+-------------+
|           2 |
|           4 |
+-------------+

Comme UNION, les deux opérations suppriment les lignes répétées. Dans l’ancien MySQL, le même résultat s’obtient avec EXISTS et NOT EXISTS de l’article Sous-requêtes, EXISTS et WITH.

La vue – une requête enregistrée

La liste des commandes avec le nom du client et le nom du produit nécessite deux JOIN. CREATE VIEW l’enregistre sous un nom et, à partir de là, vous l’utilisez comme une table :

CREATE VIEW order_overview AS
SELECT o.id, o.order_date, c.name AS customer, p.name AS product, o.quantity, o.quantity * p.price AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id;
SELECT * FROM order_overview WHERE customer = 'Peter Brown' ORDER BY order_date;

+----+------------+-------------+-------------------+----------+-------+
| id | order_date | customer    | product           | quantity | total |
+----+------------+-------------+-------------------+----------+-------+
|  3 | 2026-01-12 | Peter Brown | Coffee grinder    |        1 | 49.00 |
|  7 | 2026-03-15 | Peter Brown | Coffee beans 1 kg |        3 | 53.70 |
+----+------------+-------------+-------------------+----------+-------+

SELECT customer, SUM(total) AS spent
FROM order_overview
GROUP BY customer
ORDER BY spent DESC;

+--------------+--------+
| customer     | spent  |
+--------------+--------+
| Peter Brown  | 102.70 |
| Jane Smith   |  66.70 |
| Thomas Clark |  17.90 |
| Chloé Martin |  14.70 |
+--------------+--------+

Une vue ne stocke pas de données, seulement la requête. Elle s’exécute à nouveau à chaque lecture et montre donc toujours l’état actuel des tables. La base de données la fusionne généralement avec votre requête en une seule (algorithme MERGE) et utilise les index des tables sources. Une vue avec GROUP BY, DISTINCT ou UNION doit cependant d’abord être entièrement calculée dans une table temporaire (algorithme TEMPTABLE), ce qui ralentit sur de grandes tables.

Modifier des données par une vue et WITH CHECK OPTION

Par une vue simple sur une seule table (sans GROUP BY, DISTINCT ni fonctions d’agrégation), vous pouvez aussi modifier des données. WITH CHECK OPTION veille à ce qu’une modification ne fasse pas sortir la ligne de la vue :

CREATE VIEW products_in_stock AS
SELECT id, name, price, stock FROM products WHERE stock > 0
WITH CHECK OPTION;
UPDATE products_in_stock SET price = 5.20 WHERE id = 2;
SELECT name, price FROM products WHERE id = 2;

+-----------+-------+
| name      | price |
+-----------+-------+
| Green tea |  5.20 |
+-----------+-------+

La modification du prix est passée dans la table products. Mettre le stock à zéro retirerait cependant la ligne de la vue, et WITH CHECK OPTION ne le permet pas :

UPDATE products_in_stock SET stock = 0 WHERE id = 2;

Résultat en MariaDB :

ERROR 1369 (44000): CHECK OPTION failed `sample`.`products_in_stock`

Résultat en MySQL 8 :

ERROR 1369 (HY000): CHECK OPTION failed 'sample.products_in_stock'

Les vues de la base de données :

SELECT TABLE_NAME FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;

+-------------------+
| TABLE_NAME        |
+-------------------+
| order_overview    |
| products_in_stock |
+-------------------+

SHOW CREATE VIEW order_overview; affiche la définition de la vue, CREATE OR REPLACE VIEW la modifie et DROP VIEW la supprime.

DEFINER – pourquoi une vue cesse de fonctionner après un déplacement

Une vue retient l’utilisateur qui l’a créée (DEFINER) et, par défaut (SQL SECURITY DEFINER), lit les tables avec ses droits. Si vous déplacez la base de données sur un autre serveur où cet utilisateur n’existe pas (en local par exemple root@localhost, chez l’hébergeur un autre compte), la lecture de la vue échoue avec l’erreur 1449 « The user specified as a definer does not exist ».

Les solutions :

• Recréez la vue sur le serveur de destination (CREATE OR REPLACE VIEW sous l’utilisateur de ce serveur).
• Créez les vues avec SQL SECURITY INVOKER – elles lisent alors avec les droits de celui qui utilise la vue, et le DEFINER n’a pas d’importance :

CREATE OR REPLACE SQL SECURITY INVOKER VIEW order_overview AS
SELECT o.id, o.order_date, c.name AS customer, p.name AS product, o.quantity, o.quantity * p.price AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id;

Le DEFINER figure aussi dans les sauvegardes de mysqldump et de phpMyAdmin. Lors d’un import sur le serveur de l’hébergeur, il vaut donc la peine de le remplacer ou de le supprimer dans le fichier de sauvegarde.

Quand une vue a du sens

• Une requête complexe qui se répète à plusieurs endroits du site ou dans les rapports.
• Un accès restreint : un utilisateur de base de données peut n’avoir accès qu’à la vue, sans les colonnes sensibles.
• Une période de transition lors d’un changement de structure : l’ancien code lit la vue avec les anciens noms de colonnes.

Une vue n’accélère pas une requête par elle-même. Une requête lente reste lente sous forme de vue et vous l’examinez de la même façon – avec EXPLAIN.

Articles connexes :
Regroupement et totaux (GROUP BY)
Joindre des tables avec JOIN
Sous-requêtes, EXISTS et WITH
Sauvegarde et restauration de la base de données