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.
1. Find all books priced under $12.
2. Find all books that are either Sci-Fi or Dystopian.
3. Find all books that are Sci-Fi and currently in stock.
4. Find all books published between 1940 and 1960 (inclusive).
5. Find all books written by either 'George Orwell' or 'Isaac Asimov' — write it using IN.
6. Find all books that are not in the Sci-Fi genre.
7. Write a query for: "books by George Orwell, OR any Dystopian book published after 1940."
8. Sort all books by price, most expensive first.
9. Sort all books by genre (A-Z), and within each genre, by price (cheapest first).
10. Find the 2 cheapest books that are currently in stock.
11. Find all books where author is missing. This table has no NULL authors right now — write the query as if it did, and explain why WHERE author = NULL wouldn't work.
12. Return the title and price of all out-of-stock books, sorted by how long ago they were published (oldest first), showing only the single oldest one.
1. The core functions
SELECT COUNT(*) FROM books; -- how many rows: 5
SELECT SUM(price) FROM books; -- total of all prices
SELECT AVG(price) FROM books; -- average price
SELECT MIN(price) FROM books; -- cheapest price
SELECT MAX(price) FROM books; -- most expensive price2. GROUP BY
SELECT genre, AVG(price) AS avg_price
FROM books
GROUP BY genre;+-----------+-----------+ | genre | avg_price | +-----------+-----------+ | Sci-Fi | 13.750000 | | Dystopian | 10.120000 | | Satire | 7.500000 | +-----------+-----------+
3. HAVING — filtering after grouping
SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING COUNT(*) > 1;+-----------+------------+ | genre | book_count | +-----------+------------+ | Sci-Fi | 2 | | Dystopian | 2 | +-----------+------------+
4. Combining WHERE and HAVING
SELECT genre, AVG(price) AS avg_price
FROM books
WHERE in_stock = TRUE
GROUP BY genre
HAVING AVG(price) > 10;+-----------+-----------+ | genre | avg_price | +-----------+-----------+ | Sci-Fi | 15.500000 | | Dystopian | 10.120000 | +-----------+-----------+