Set operation column matching rules in SQL

Introduction

Set operations combine results from two or more queries. The columns must match so the database knows how to join the data.

You want to combine lists of customers from two different stores.

You need to merge product lists from two suppliers into one list.

You want to find common items sold in two different years.

You want to get all unique email addresses from two mailing lists.

Syntax

SQL

SELECT column1, column2, ... FROM table1
SET_OPERATION
SELECT column1, column2, ... FROM table2;

Both SELECT statements must have the same number of columns.

The data types of corresponding columns should be compatible.

Examples

This combines employee and contractor names and ages into one list without duplicates.

SQL

SELECT name, age FROM employees
UNION
SELECT name, age FROM contractors;

This finds products with the same ID and price in both stores.

SQL

SELECT product_id, price FROM store1
INTERSECT
SELECT product_id, price FROM store2;

This shows cities and states where customers are only in the north region.

SQL

SELECT city, state FROM customers_north
EXCEPT
SELECT city, state FROM customers_south;

Sample Program

This example creates two fruit tables and combines their lists without duplicates using UNION.

SQL

CREATE TABLE fruits1 (name VARCHAR(20), color VARCHAR(20));
INSERT INTO fruits1 VALUES ('Apple', 'Red'), ('Banana', 'Yellow');

CREATE TABLE fruits2 (name VARCHAR(20), color VARCHAR(20));
INSERT INTO fruits2 VALUES ('Banana', 'Yellow'), ('Grape', 'Purple');

SELECT name, color FROM fruits1
UNION
SELECT name, color FROM fruits2;

Important Notes

Column names in the final result come from the first SELECT statement.

UNION removes duplicates, UNION ALL keeps duplicates.

Data types should be compatible but do not need to be exactly the same.

Summary

Set operations combine results from multiple queries.

Columns must match in number and compatible types.

Column names come from the first query in the set operation.