Warming up the neural circuits...
A real-world database might have millions of rows. You never want to see all of them at once. By the end of this module, you'll be able to:
The WHERE clause is the "Filter" of the world.
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | WHERE category = 'Electronics' |
<> or != | Not equal to | WHERE status <> 'cancelled' |
> or < | Greater/Less than | WHERE price > 500 |
>= or <= | Greater/Less or Equal | WHERE age >= 18 |
-- Both must be true
SELECT * FROM products
WHERE category = 'Accessories' AND price < 1000;
-- Either can be true
SELECT * FROM products
WHERE category = 'Electronics' OR category = 'Gadgets';
--
Without ORDER BY, the database returns rows in whatever order it finds them. Never rely on the "default" order.
-- Sort by price (Lowest to Highest)
SELECT * FROM products ORDER BY price;
-- Sort by price (Highest to Lowest)
SELECT * FROM products ORDER BY price DESC;
-- Multi-Column Sort
-- Sort by category first, then by price within that category
SELECT * FROM products
ORDER BY category ASC, price DESC;When you scroll through products on Amazon, you are seeing . You are only seeing 20-50 items at a time.
-- Show only the first 5 results
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 5;
-- Skip the first 5, show the next 5 (Page 2)
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 5 OFFSET 5;To get Page N with S items per page:
LIMIT S OFFSET (N - 1) * S
Performance Note: OFFSET gets slower as the number increases. To skip 1,000,000 rows, Postgres still has to "read" them all before throwing them away. For very large datasets, senior engineers use Keyset Pagination (ask about this in the advanced course!).
Instead of writing many OR or AND statements, use these shortcuts:
-- Instead of: price >= 100 AND price <= 500
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
-- Instead of: id = 1 OR id = 5 OR id = 10
SELECT * FROM products WHERE id IN (1, 5, 10);Using your courses table:
LIMIT and OFFSET.Pro Tip: Always sort your data before using LIMIT. If you don't use ORDER BY, "Page 1" and "Page 2" might contain the same items because the database doesn't guarantee a specific order!