Set operations UNION, INTERSECT, and EXCEPT in MariaDB
summary
Shows exactly how UNION, INTERSECT and EXCEPT behave in MariaDB, including that UNION is DISTINCT by default, UNION ALL returns 10 rows in the example vs 8 with DISTINCT, and why missing parentheses turn a trailing ORDER BY into a global sort. Explains how EXPLAIN EXTENDED + SHOW WARNINGS reveals server rewrites, describes temp-table execution with a UNIQUE index and hidden duplicate counter / marked-rows fields for ALL and DISTINCT semantics, and lists version milestones (10.1–10.5).
AI-written summary · about
chapters (10)
- 0:00Introduction to set operations
- 1:58UNION with DISTINCT default
- 3:55Importance of brackets in queries
- 5:31INTERSECT and EXCEPT basics
- 6:44INTERSECT ALL and EXCEPT ALL
- 8:27UNION ALL optimization
- 10:04Operation precedence rules
- 12:10Historical development in MariaDB
- 13:46Internals with EXPLAIN EXTENDED
- 17:43Complex ALL operations internals
related talks
34mCommon Table Expressions and Window Functions simple, maintainable, fast queries
44mBeyond WHERE and GROUP BY
53mMariaDB Server 10.2: The Complete Guide
21mWriting High Performance SQL Statements
47mMySQL Indexing Best Practices
25mThe Secret Life of SQL: How to Optimize Database Performance
32mTwo opensource RDBMS: mariadb vs postgresql
42mThings every developer absolutely, positively needs to know about database indexing