Writing SQL SELECT Queries
1. Selecting Columns
| Form | Meaning |
|---|
SELECT * | All columns (avoid in production code) |
SELECT col1, col2 | Explicit projection |
SELECT t.col | Qualified by table/alias |
SELECT expr AS name | Computed column |
SELECT DISTINCT col | Unique values |
2. Filtering Rows
| Operator | Example |
|---|
| =, <>, <, <=, >, >= | WHERE price > 100 |
| BETWEEN | WHERE age BETWEEN 18 AND 65 |
| IN / NOT IN | WHERE status IN ('open','pending') |
| LIKE / ILIKE | WHERE name ILIKE 'a%' |
| IS NULL / IS NOT NULL | WHERE deleted_at IS NULL |
| EXISTS | WHERE EXISTS (SELECT 1 FROM ...) |
3. Using Logical Operators
| Operator | Truth Table Notes |
|---|
| AND | TRUE only if both TRUE; NULL propagates |
| OR | TRUE if either TRUE |
| NOT | Inverts TRUE/FALSE; NOT NULL = NULL |
| Precedence | NOT > AND > OR — use parentheses |
4. Filtering with Ranges
| Pattern | Example |
|---|
| Inclusive range | BETWEEN 10 AND 20 |
| Exclusive | col > 10 AND col < 20 |
| Date range | created_at >= DATE '2026-01-01' AND created_at < DATE '2026-02-01' |
| Range type (Postgres) | price <@ numrange(10, 20, '[)') |
Note: For dates, prefer >= start AND < end over BETWEEN — avoids end-of-day boundary bugs.
5. Matching Patterns
| Operator | Use |
|---|
| LIKE 'a%' | Starts with a (index-friendly) |
| LIKE '%a' | Ends with a (full scan unless reverse index) |
| LIKE '%a%' | Contains a (no index help — use FTS) |
| ILIKE | Case-insensitive LIKE (Postgres) |
| SIMILAR TO | SQL regex syntax |
| ~ ~* !~ !~* (PG) | POSIX regex match |
6. Handling NULL Values
| Function | Behavior |
|---|
| IS NULL / IS NOT NULL | Only correct null test |
| COALESCE(a, b, c) | First non-null |
| NULLIF(a, b) | NULL if a = b |
| IS DISTINCT FROM | NULL-safe inequality |
| Aggregates | Skip NULLs (except COUNT(*)) |
Warning: col = NULL is always UNKNOWN (not TRUE). Use IS NULL.
7. Sorting Results
| Clause | Effect |
|---|
ORDER BY col | Ascending by default |
ORDER BY col DESC | Descending |
ORDER BY col1, col2 DESC | Multi-column |
NULLS FIRST / LAST | Control NULL position |
ORDER BY 1, 2 | By column position (avoid in code) |
8. Limiting Results
| DB | Syntax |
|---|
| Standard SQL:2008 | FETCH FIRST n ROWS ONLY |
| Postgres / MySQL | LIMIT n OFFSET m |
| SQL Server | OFFSET m ROWS FETCH NEXT n ROWS ONLY |
| Oracle 12c+ | FETCH FIRST n ROWS ONLY |
| Keyset pagination | WHERE (created_at, id) < (?, ?) ORDER BY ... LIMIT n |
Note: Avoid large OFFSETs — they scan and discard rows. Prefer keyset (seek) pagination.
9. Removing Duplicates
| Approach | Use |
|---|
DISTINCT | Remove duplicate rows across selected columns |
DISTINCT ON (col) (PG) | One row per group, requires matching ORDER BY |
GROUP BY | Aggregate-aware deduplication |
ROW_NUMBER() | Filter to nth row per partition |
UNION | Set union with dedup (vs UNION ALL) |
10. Aliasing Columns and Tables
| Form | Example |
|---|
| Column alias | SELECT price * qty AS total |
| Quoted alias | AS "Total Sales" |
| Table alias | FROM orders AS o |
| Implicit (no AS) | FROM orders o |
| Alias in WHERE | Not allowed — only ORDER BY/HAVING (use subquery or CTE) |