Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 40 ยท SQL Basics

UNION, INTERSECT and EXCEPT in MariaDB

Combine query results in MariaDB with UNION, UNION ALL, INTERSECT and EXCEPT, learn the column rules, ordering and limits of combined queries, and see the older emulations.

A join combines tables sideways, adding columns. Set operators combine query results vertically, stacking rows from two or more SELECT statements. MariaDB supports UNION, UNION ALL, INTERSECT and EXCEPT (the last two since version 10.3). This lesson shows what each returns and the rules they follow.

The rules

Every query in a combination must return:

  • the same number of columns,
  • in the same order,
  • with compatible types (MariaDB converts when it can).

The column names of the result come from the first SELECT. ORDER BY and LIMIT can be applied to the whole combination by placing them at the end.

UNION and UNION ALL

UNION stacks the results and removes duplicate rows. UNION ALL keeps all rows, including duplicates, and is faster because it skips the duplicate search.

SQL UNION removes duplicates, UNION ALL keeps them ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Use UNION ALL by default unless you really want duplicates removed. A very common use is to combine data that lives in several tables of the same shape, such as an active table and an archive, or to add a total row to a report:

SQL Detail rows plus a total row ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

When you label rows from different sources, add a constant column so you can tell where each came from:

SQL Tag the source of each row ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

ORDER BY and LIMIT

A trailing ORDER BY sorts the final combined result and can refer only to column names or positions of the first query. To sort or limit an individual member, wrap it in parentheses (MariaDB supports this):

SQL
(SELECT name, price FROM products ORDER BY price DESC LIMIT 3)
UNION ALL
(SELECT name, price FROM products ORDER BY price ASC LIMIT 3);

SQLite does not accept parentheses around each member, so the runnable version uses subqueries:

SQL Three dearest and three cheapest ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

INTERSECT

INTERSECT returns the rows that appear in both results (duplicates removed). Typical question: which customers are in both group A and group B?

SQL Customers who ordered both a delivered and a cancelled order ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

MariaDB also has INTERSECT ALL (keeps multiplicity) since 10.5. If you work on an older server, a join or EXISTS gives the same result:

SQL
SELECT DISTINCT a.customer_id
FROM orders a JOIN orders b ON b.customer_id = a.customer_id
WHERE a.status = 'delivered' AND b.status = 'cancelled';

EXCEPT

EXCEPT (also written MINUS in Oracle-compatibility mode) returns rows from the first result that are not in the second. Order matters: A EXCEPT B differs from B EXCEPT A.

SQL Customers who have never ordered ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The equivalent anti-join, which works on every version:

SQL The same with NOT EXISTS ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Precedence

When you chain several operators, INTERSECT binds tighter than UNION and EXCEPT, which are evaluated left to right. Use parentheses to be explicit:

SQL
(SELECT id FROM a UNION SELECT id FROM b)
EXCEPT
SELECT id FROM c;
flowchart LR
  subgraph U["UNION"]
    A1["A"] --- B1["B"]
  end
  subgraph I["INTERSECT"]
    A2["A"] --- B2["B"]
  end
  subgraph E["EXCEPT"]
    A3["A"] --- B3["B"]
  end

UNION keeps A or B, INTERSECT keeps what both share, EXCEPT keeps A without B.

Full outer join with UNION

MariaDB has no FULL OUTER JOIN. You can build it with two outer joins and UNION, which you will practise in joins in MariaDB.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between UNION and UNION ALL?

    UNION deduplicates (extra work); UNION ALL just stacks the rows and is faster.
  2. 2. Where do the column names of a UNION result come from?

    The first query decides the names; later queries only need matching column count and compatible types.
  3. 3. Which operator returns rows from the first query that are NOT in the second?

    EXCEPT subtracts the second result from the first.
  4. 4. Which MariaDB version added INTERSECT and EXCEPT?

    Version 10.3 added them. Older servers need joins or NOT EXISTS.

Summary

  • Set operators stack results: same column count, compatible types, names from the first query.
  • UNION removes duplicates; UNION ALL keeps them and is faster.
  • INTERSECT keeps common rows; EXCEPT keeps rows of the first query missing from the second.
  • Equivalent joins or NOT EXISTS work on any version.

Next: Data types in MariaDB