Academic Block

Academic Block logo
Menu
SQL UNION
Learn how to combine the results of two or more SELECT statements into a single result set using SQL UNION.

What is the UNION Operator?

The UNION operator combines the results of two or more SELECT statements into a single result set. By default, UNION removes duplicate rows from the final result.

Basic UNION Syntax

Each SELECT statement used with UNION must return the same number of columns, and corresponding columns should contain compatible types. 

DROP TABLE IF EXISTS morning_students;
DROP TABLE IF EXISTS evening_students;

CREATE TABLE morning_students (
  student_id INTEGER,
  student_name TEXT
);

CREATE TABLE evening_students (
  student_id INTEGER,
  student_name TEXT
);

INSERT INTO morning_students VALUES
  (1, 'Aarav'),
  (2, 'Meera'),
  (3, 'Kabir');

INSERT INTO evening_students VALUES
  (4, 'Riya'),
  (5, 'Dev'),
  (6, 'Tara');

SELECT student_id, student_name
FROM morning_students

UNION

SELECT student_id, student_name
FROM evening_students
ORDER BY student_id;

UNION Removes Duplicate Rows

One of the main features of UNION is that duplicate rows are removed automatically from the final result. 

DROP TABLE IF EXISTS online_courses;
DROP TABLE IF EXISTS classroom_courses;

CREATE TABLE online_courses (
  course_name TEXT
);

CREATE TABLE classroom_courses (
  course_name TEXT
);

INSERT INTO online_courses VALUES
  ('SQL'),
  ('Python'),
  ('HTML');

INSERT INTO classroom_courses VALUES
  ('SQL'),
  ('Java'),
  ('CSS');

SELECT course_name
FROM online_courses

UNION

SELECT course_name
FROM classroom_courses
ORDER BY course_name;

UNION ALL

UNION ALL combines results without removing duplicates. This means every row from both SELECT statements is included. 

DROP TABLE IF EXISTS store_a;
DROP TABLE IF EXISTS store_b;

CREATE TABLE store_a (
  product_name TEXT
);

CREATE TABLE store_b (
  product_name TEXT
);

INSERT INTO store_a VALUES
  ('Keyboard'),
  ('Mouse'),
  ('Monitor');

INSERT INTO store_b VALUES
  ('Mouse'),
  ('Monitor'),
  ('Webcam');

SELECT product_name
FROM store_a

UNION ALL

SELECT product_name
FROM store_b
ORDER BY product_name;

UNION with Different Tables

UNION can combine data from completely different tables as long as the SELECT statements return compatible columns. 

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS suppliers;

CREATE TABLE customers (
  person_name TEXT,
  city TEXT
);

CREATE TABLE suppliers (
  person_name TEXT,
  city TEXT
);

INSERT INTO customers VALUES
  ('Nisha', 'Delhi'),
  ('Rohan', 'Pune');

INSERT INTO suppliers VALUES
  ('Karan', 'Mumbai'),
  ('Tanya', 'Jaipur');

SELECT person_name, city
FROM customers

UNION

SELECT person_name, city
FROM suppliers
ORDER BY person_name;

UNION with a WHERE Clause

Each SELECT statement can have its own WHERE clause before the UNION. This allows you to filter each dataset independently. 

DROP TABLE IF EXISTS north_orders;
DROP TABLE IF EXISTS south_orders;

CREATE TABLE north_orders (
  order_id INTEGER,
  customer_name TEXT,
  amount INTEGER
);

CREATE TABLE south_orders (
  order_id INTEGER,
  customer_name TEXT,
  amount INTEGER
);

INSERT INTO north_orders VALUES
  (101, 'Aditi', 1200),
  (102, 'Vikram', 450),
  (103, 'Neha', 1800);

INSERT INTO south_orders VALUES
  (201, 'Arjun', 900),
  (202, 'Pooja', 2200),
  (203, 'Mohan', 700);

SELECT order_id, customer_name, amount
FROM north_orders
WHERE amount >= 1000

UNION

SELECT order_id, customer_name, amount
FROM south_orders
WHERE amount >= 1000
ORDER BY amount DESC;

UNION with Calculated Columns

UNION can also combine SELECT statements containing calculated values. Both SELECT statements must still return the same number of columns. 

DROP TABLE IF EXISTS physical_sales;
DROP TABLE IF EXISTS digital_sales;

CREATE TABLE physical_sales (
  product TEXT,
  quantity INTEGER
);

CREATE TABLE digital_sales (
  product TEXT,
  downloads INTEGER
);

INSERT INTO physical_sales VALUES
  ('Notebook', 25),
  ('Planner', 18);

INSERT INTO digital_sales VALUES
  ('Ebook', 40),
  ('Template', 30);

SELECT
  product,
  quantity AS units,
  'Physical' AS sale_type
FROM physical_sales

UNION

SELECT
  product,
  downloads AS units,
  'Digital' AS sale_type
FROM digital_sales
ORDER BY units DESC;

UNION with Three SELECT Statements

You can combine more than two SELECT statements using UNION. Each query contributes its results to the final result set. 

DROP TABLE IF EXISTS delhi_products;
DROP TABLE IF EXISTS mumbai_products;
DROP TABLE IF EXISTS pune_products;

CREATE TABLE delhi_products (
  product_name TEXT
);

CREATE TABLE mumbai_products (
  product_name TEXT
);

CREATE TABLE pune_products (
  product_name TEXT
);

INSERT INTO delhi_products VALUES
  ('Laptop'),
  ('Keyboard');

INSERT INTO mumbai_products VALUES
  ('Monitor'),
  ('Mouse');

INSERT INTO pune_products VALUES
  ('Webcam'),
  ('Laptop');

SELECT product_name
FROM delhi_products

UNION

SELECT product_name
FROM mumbai_products

UNION

SELECT product_name
FROM pune_products
ORDER BY product_name;

UNION with Column Aliases

Column aliases can be used to give meaningful names to the combined result. The column names of the final result normally come from the first SELECT statement. 

DROP TABLE IF EXISTS employees_2025;
DROP TABLE IF EXISTS employees_2026;

CREATE TABLE employees_2025 (
  name TEXT,
  department TEXT
);

CREATE TABLE employees_2026 (
  name TEXT,
  department TEXT
);

INSERT INTO employees_2025 VALUES
  ('Ishita', 'Design'),
  ('Rahul', 'Finance');

INSERT INTO employees_2026 VALUES
  ('Mira', 'Engineering'),
  ('Dev', 'Marketing');

SELECT
  name AS employee_name,
  department AS team
FROM employees_2025

UNION

SELECT
  name,
  department
FROM employees_2026
ORDER BY employee_name;

UNION with ORDER BY

When using ORDER BY with UNION, place the ORDER BY clause at the end of the complete UNION query to sort the final result. 

DROP TABLE IF EXISTS first_team;
DROP TABLE IF EXISTS second_team;

CREATE TABLE first_team (
  member_name TEXT,
  score INTEGER
);

CREATE TABLE second_team (
  member_name TEXT,
  score INTEGER
);

INSERT INTO first_team VALUES
  ('Aman', 75),
  ('Riya', 92);

INSERT INTO second_team VALUES
  ('Kabir', 84),
  ('Tina', 68);

SELECT member_name, score
FROM first_team

UNION

SELECT member_name, score
FROM second_team

ORDER BY score DESC;

UNION vs UNION ALL

Feature UNION UNION ALL
Combines results Yes Yes
Removes duplicates Yes No
Keeps duplicate rows No Yes
Typical use Unique combined results Keep every row

Rules for UNION

  • Each SELECT statement must return the same number of columns.
  • Corresponding columns should have compatible data types.
  • The columns appear in the order specified by the first SELECT statement.
  • UNION removes duplicate rows automatically.
  • UNION ALL keeps duplicate rows.
  • Use ORDER BY at the end to sort the combined result.

Common Uses of UNION

  • Combining data from multiple tables with the same structure.
  • Combining records from different locations or departments.
  • Creating a single list from several datasets.
  • Combining historical and current records.
  • Comparing or consolidating data from different sources.

Best Practices

  • Use UNION when duplicate rows should be removed.
  • Use UNION ALL when every row should be preserved.
  • Keep the column order consistent between SELECT statements.
  • Use meaningful aliases in the first SELECT statement.
  • Place ORDER BY after the complete UNION query.
  • Use parentheses carefully when combining UNION with more complex queries.

The UNION operator is useful for combining results from multiple SELECT statements into one result set. It removes duplicate rows by default, while UNION ALL keeps every row.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code
👁️ Preview (query result)

Click “Run Code” to see the result here.