The Story — Two Guest Lists, One Wedding

The Sample Database

Set Operators — The Venn Picture

UNION · INTERSECT · EXCEPT in Action

SELECT cust_id, name FROM online_buyers
UNION
SELECT cust_id, name FROM store_buyers
ORDER BY cust_id;

SELECT cust_id, name FROM online_buyers
INTERSECT
SELECT cust_id, name FROM store_buyers;

SELECT cust_id, name FROM online_buyers
EXCEPT
SELECT cust_id, name FROM store_buyers;

Set Operator Rules at a Glance

Views — A Saved Query That Acts Like a Table

A view is a window onto your data, not a copy. You name a query once; every SELECT from the view runs it fresh against the live base tables.

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;

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

Indexes — The Speed Mechanism

An index is the index at the back of a textbook: instead of reading all 900 pages, you jump straight to the right one. The engine uses a sorted B-tree instead of scanning every row.

CREATE INDEX idx_emp_dept ON employees(dept_id);              -- simple
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary);  -- composite
CREATE UNIQUE INDEX idx_emp_email ON employees(email);       -- unique

Indexes Are Not Free

Joins — Stitching Tables Side by Side

A join matches rows from two tables on a related column and merges them into one wider row. Four flavours differ only in which unmatched rows they keep.

INNER JOIN — Only Matching Rows

SELECT e.name, d.dept_name
FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;
-- Asha·Eng, Bilal·Eng, Chen·Sales, Diya·Marketing  (Esha & Research dropped)

OUTER JOINs — Keep the Unmatched

Outer joins preserve rows with no match, filling the missing side with NULL.

-- LEFT: all employees, dept if any
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id;

-- RIGHT: all departments, staff if any
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d
  ON e.dept_id = d.dept_id;

SELF JOIN — A Table Joined to Itself

A manager is an employee — manager_id points to another emp_id in the same table. So the table plays two roles at once.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;
-- Asha→NULL, Bilal→Asha, Chen→Asha, Diya→Bilal, Esha→Bilal

Joins vs Set Operators — Side by Side

Golden Rules

Four Tools, One Toolbox

🔗 End of tutorial · Press ← to review, or click Restart