Querying
A JOIN combines rows from two (or more) tables based on a related column — usually a foreign key pointing to a primary key.
Instead of storing everything in one giant table, related data lives in separate tables, and joins stitch it back together when you need it.
- INNER JOIN: Return only rows where both tables have matching data.
- LEFT JOIN: Return all rows from the left table, plus matching rows from the right table. If no match, the right table's columns come back as NULL.
- RIGHT JOIN: Return all rows from the right table, plus matching rows from the left table. If no match, the left table's columns come back as NULL.
- CROSS JOIN: Return the Cartesian product of the two tables. Not supported in all SQL databases.
- SELF JOIN: Return rows from a table that match rows from the same table.
-- Inner Join
SELECT customers.name, orders.product
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;
-- Left Join
SELECT customers.name, orders.product, orders.price
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;
-- Right Join
SELECT customers.name, orders.product, orders.price
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customer_id;
-- Cross Join
SELECT customers.name, orders.product, orders.price
FROM customers
CROSS JOIN orders;
-- Self Join
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
INNER JOIN Employees m
ON e.ManagerID = m.EmpID;| Concept | Key takeaway | Example |
|---|---|---|
| AND | Both conditions must be true | WHERE a AND b |
| OR | Either condition can be true | WHERE a OR b |
| Operator precedence | AND evaluates before OR — always add parentheses when mixing them | WHERE (a OR b) AND c |
| NOT | Inverts a condition | WHERE NOT genre = 'Sci-Fi' |
| BETWEEN | Inclusive range check | WHERE price BETWEEN 10 AND 15 |
| IN | Matches against a list — cleaner than chained OR | WHERE author IN ('A', 'B') |
| NULL handling | = NULL never matches — use IS NULL / IS NOT NULL | WHERE genre IS NULL |
| ORDER BY (multiple columns) | Second column only breaks ties in the first | ORDER BY genre ASC, price ASC |
| ORDER BY (expression) | Can sort by a calculated value, not just a raw column | ORDER BY (2026 - published_year) DESC |
One-line recap
WHERE filters which rows come back, AND/OR combine conditions (parenthesize when mixed), IN/BETWEEN simplify common patterns, NULL needs IS NULL not =, and ORDER BY controls result order — by column, multiple columns, or an expression.