MariaDB Tutorial / Combine Query Results with UNION and UNION ALL

UNION stacks compatible query results and removes duplicate selected rows; UNION ALL keeps duplicates. Match the column count, position and meaning, then use a final ORDER BY when the combined output needs a defined sequence.

Distinguish stacking rows from joining related columns

Adding order information beside a customer uses JOIN. Combining Seoul customers with paid-order customers into one target population uses UNION. Corresponding column positions must have the same meaning: a customer identifier and an order identifier are not interchangeable merely because both are integers. As with DISTINCT in Select Columns, Aliases, Calculations, and DISTINCT, duplicate detection considers the selected combination; this differs from joining related rows through the keys in Read Related Rows with INNER JOIN.

Check the MariaDB documentation for the syntax and conditions used here: UNION.

Prepare two overlapping customer populations

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c30_customers, c30_products, c30_orders, c30_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE c30_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c30_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c30_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c30_order_customer FOREIGN KEY (customer_id)
  REFERENCES c30_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c30_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c30_item_order FOREIGN KEY (order_id)
  REFERENCES c30_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c30_item_product FOREIGN KEY (product_id)
  REFERENCES c30_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c30_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c30_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c30_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c30_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Seoul customers are IDs 1 and 3, while paid-order customers are 1 and 2. Customer 1 belongs to both. The paid-order branch uses EXISTS so its two orders still contribute only one customer within that branch.

Compare the UNION and UNION ALL results

SELECT customer_id FROM c30_customers WHERE city='서울'
UNION
SELECT c.customer_id FROM c30_customers c WHERE EXISTS
 (SELECT 1 FROM c30_orders o WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY customer_id;
SELECT customer_id FROM c30_customers WHERE city='서울'
UNION ALL
SELECT c.customer_id FROM c30_customers c WHERE EXISTS
 (SELECT 1 FROM c30_orders o WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY customer_id;

UNION returns three rows: 1, 2 and 3. UNION ALL returns four: 1, 1, 2 and 3. The repeated ID 1 preserves membership in both input lists. UNION ALL can avoid duplicate-elimination work, but discarding a required uniqueness rule merely for speed produces the wrong target population.

Recognize how a source-label column changes equality

SELECT customer_id,'서울 고객' AS source_label FROM c30_customers WHERE city='서울'
UNION
SELECT c.customer_id,'결제 고객' FROM c30_customers c WHERE EXISTS
 (SELECT 1 FROM c30_orders o WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY customer_id,source_label;

The two Korean source labels mean Seoul customer and paid-order customer. Because those labels differ, customer 1 keeps two rows even with UNION. Deduplication compares the entire selected combination, not just customer_id. One row per customer with several displayed sources needs a separate aggregation or presentation design.

Align column counts, compatible types and final ordering

Each branch must return the same number of columns with compatible positional types. Final output names normally come from the first SELECT list, so aliases in later branches do not independently rename them. Inspect conversions when combining strings and numbers.

The final ORDER BY orders the complete combined result. Branch-level ordering alone does not guarantee that overall order. When a branch needs its own top-N selection, make parentheses and LIMIT scope explicit and distinguish that selection from the final sort. Deduplication can also change source counts, so keep occurrence counting separate from unique-customer counting.

Review each input result before combining it

Run the branches independently and compare column meaning and row count. Inspect UNION ALL first to see repeated memberships, then decide whether removing identical results is required. UNION changes the query output; it neither deletes source rows nor permanently merges the underlying tables.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.c30_items;
DROP TABLE shopcourse.c30_orders;
DROP TABLE shopcourse.c30_products;
DROP TABLE shopcourse.c30_customers;