UNION and views (VIEW)

The examples work with the sample database from the SQL basics series (the customers, products and orders tables). Before experimenting, return the database to its original state by running its script again. The results are real outputs from MySQL 8.0 and MariaDB 10.4, where they differ, both are shown.

UNION ALL – results one below the other

The search on a website should find both products and customers. UNION combines two queries over different tables into one result:

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 |
+----------+----------------+

Rules for UNION:

• All parts must have the same number of columns. The types are unified, a number and a text give a text.
• The column names of the result are taken from the first part (that is why only it has aliases).
• ORDER BY and LIMIT at the end apply to the whole result.

UNION and UNION ALL

Which customers bought coffee or tea?

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 on its own removes repeated rows. UNION ALL keeps them:

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 |
+-------------+

Customer number 1 bought both, which is why she is listed twice. Removing duplicates takes time (the result has to be compared), so write UNION ALL wherever the parts do not overlap or where duplicates do not matter. Use UNION on its own only where you really do not want repeated rows.

ORDER BY and LIMIT in individual parts

A part in parentheses can have its own sorting and limit. The most expensive and the cheapest product in one result:

(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 |
+----------------+----------------+-------+

A total row – WITH ROLLUP

A row with the grand total is often added to reports with UNION ALL. A simpler way is GROUP BY … WITH ROLLUP, which adds the total row by itself. In the column used for grouping, the total row has NULL. COALESCE replaces it with text:

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 and difference – INTERSECT and EXCEPT

INTERSECT returns the rows that are in both results, EXCEPT the rows of the first result that are not in the second one. MariaDB supports them since version 10.3, MySQL only since version 8.0.31. Who bought both coffee and tea:

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

Result in MariaDB:

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

Who bought coffee but not tea:

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

Result in MariaDB:

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

Like UNION, both operations remove repeated rows. In older MySQL, EXISTS and NOT EXISTS from the article Subqueries, EXISTS and WITH give the same result.

A view – a stored query

A listing of orders with the name of the customer and the name of the product needs two JOINs. CREATE VIEW stores it under a name and from then on you work with it like with a 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 |
+--------------+--------+

A view stores no data, only the query. It runs again on every read, so it always shows the current state of the tables. The database usually merges it with your query into one (the MERGE algorithm) and uses the indexes of the underlying tables. A view with GROUP BY, DISTINCT or UNION, however, has to be calculated in full into a temporary table first (the TEMPTABLE algorithm), which slows things down with large tables.

Changing data through a view and WITH CHECK OPTION

Through a simple view over one table (without GROUP BY, DISTINCT and aggregate functions), you can also change data. WITH CHECK OPTION ensures that a change does not move a row out of the view:

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 |
+-----------+-------+

The price change went through to the products table. Setting the stock to zero, however, would remove the row from the view, and WITH CHECK OPTION does not allow that:

UPDATE products_in_stock SET stock = 0 WHERE id = 2;

Result in MariaDB:

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

Result in MySQL 8:

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

The views in the database:

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; shows the definition of a view, CREATE OR REPLACE VIEW changes it and DROP VIEW deletes it.

DEFINER – why a view stops working after a move

A view remembers the user who created it (DEFINER) and by default (SQL SECURITY DEFINER) reads the tables with that user’s privileges. When you move the database to another server where no such user exists (locally, for example, root@localhost, on the hosting another account), reading the view fails with error 1449 “The user specified as a definer does not exist”.

Solutions:

• Create the view again on the target server (CREATE OR REPLACE VIEW under the user there).
• Create views with SQL SECURITY INVOKER – they then read with the privileges of whoever uses the view, and the DEFINER does not matter:

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;

The DEFINER is also in backups from mysqldump and phpMyAdmin. When importing to a hosting server, it is therefore worth replacing or removing it in the backup file.

When to use a view

• A complex query that is repeated in several places of the website or in reports.
• Restricting access: a database user can be granted access only to a view without sensitive columns.
• A transition period when changing the structure: old code reads a view with the old column names.

A view does not make a query faster by itself. A slow query is slow as a view too, and you look into it the same way – with EXPLAIN.

Related articles:
Grouping and totals (GROUP BY)
Joining tables with JOIN
Subqueries, EXISTS and WITH
Database backup and restore