Set Operators, Views, Indexes & Joins in SQL

The Story That Explains Set Operators

This tutorial walks through the six tools every database practitioner must master to combine and accelerate queries: the three set operators (UNION, INTERSECT, EXCEPT), plus Views, Indexes, and the three families of Joins (Inner, Outer, Self). Every concept is grounded in one small, consistent dataset so you can trace each result by hand.

The Sample Database We Will Use

Every example below uses these tables. Keep them in view — tracing results by hand is how the operators truly click.

Set Operators — The Animated Venn Diagram

All three set operators act on two result sets that must be union-compatible: same number of columns, in the same order, with compatible data types. The animated diagram below cycles through what each operator keeps, using our two buyer lists.

UNION — Combine and Deduplicate

UNION stacks two result sets and removes duplicate rows. UNION ALL does the same but keeps duplicates — and is much faster because it skips the deduplication step.

-- Every customer who bought through any channel
SELECT cust_id, name FROM online_buyers
UNION
SELECT cust_id, name FROM store_buyers
ORDER BY cust_id;

INTERSECT — Rows Common to Both

INTERSECT returns only rows that appear in both result sets — our "friends of both" from the wedding story. Duplicates are removed automatically.

-- Customers who bought BOTH online and in store
SELECT cust_id, name FROM online_buyers
INTERSECT
SELECT cust_id, name FROM store_buyers;

EXCEPT — In One Set but Not the Other

EXCEPT (called MINUS in Oracle) returns rows from the first query that are not present in the second. Order matters: A EXCEPT B is different from B EXCEPT A.

-- Online-only customers (never bought in store)
SELECT cust_id, name FROM online_buyers
EXCEPT
SELECT cust_id, name FROM store_buyers;

-- Oracle uses MINUS instead of EXCEPT:
-- SELECT cust_id FROM online_buyers MINUS SELECT cust_id FROM store_buyers;

Set Operator Rules at a Glance

Views — A Saved Query That Acts Like a Table

-- Create a reusable view joining employees to their department
CREATE VIEW staff_dirs AS
SELECT e.name, d.dept_name, e.salary
FROM   employees e
JOIN   departments d ON e.dept_id = d.dept_id;

-- Now query it like an ordinary table
SELECT name, dept_name
FROM   staff_dirs
WHERE  salary > 50000;

Indexes — The Speed Mechanism

-- Create an index on a frequently-filtered column
CREATE INDEX idx_emp_dept ON employees(dept_id);

-- A composite (multi-column) index — order matters!
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);

-- Enforce uniqueness AND speed lookups at once
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

Joins — Stitching Tables Side by Side

Where set operators stack rows vertically, a join matches rows from two tables on a related column and merges their columns into one wider row. The diagram below shows the four families and exactly which rows survive each one.

INNER JOIN — Only Matching Rows

An INNER JOIN returns only rows where the join key exists in both tables. Watch the animation below "draw" the matches between employees and departments.

SELECT e.name, d.dept_name
FROM   employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

OUTER JOINs — Keep the Unmatched

Outer joins preserve rows that have no match, filling the missing side with NULL. There are three flavours.

-- LEFT: every employee, even those with no department
SELECT e.name, d.dept_name
FROM   employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- FULL: every employee AND every department
SELECT e.name, d.dept_name
FROM   employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

SELF JOIN — A Table Joined to Itself

-- List each employee next to their manager's name
SELECT e.name AS employee, m.name AS manager
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Joins vs Set Operators — Side by Side

Practice Problems

Try each before revealing the logic. Use the sample tables from Section 02.

-- Q3 solution: store-only customers
SELECT cust_id, name FROM store_buyers
EXCEPT
SELECT cust_id, name FROM online_buyers;

-- Q4 solution: all departments, staffed or not
SELECT d.dept_name, e.name
FROM   departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id;

Golden Rules