SQL fundamentals — SELECT, FROM, WHERE, ORDER BY, LIMIT

Beginner5 min read·eng-06-001

sql

Concept

SQL fundamentals — the four most-used clauses of a SELECT statement form the backbone of every query you'll write.

SELECT: Which columns (or expressions) to include in the result set. SELECT * returns all columns — avoid in production (over-fetches, breaks when columns are added). SELECT name, email returns only those columns.

FROM: Which table(s) to query. FROM users — the primary source of rows.

WHERE: Filters rows. Evaluated on every row BEFORE grouping. Supports =, !=, <, >, <=, >=, BETWEEN, IN, NOT IN, IS NULL, IS NOT NULL, LIKE, AND, OR, NOT.

ORDER BY: Sorts the result. ASC (default, ascending) or DESC (descending). Can sort by multiple columns: ORDER BY last_name ASC, first_name ASC. Without ORDER BY, the order of rows is NOT guaranteed (even if it looks consistent).

LIMIT and OFFSET: Restrict the number of rows returned. LIMIT 10 — first 10 rows. LIMIT 10 OFFSET 20 — rows 21-30 (pagination). PostgreSQL uses LIMIT/OFFSET. MySQL uses the same. SQL Server uses TOP or FETCH NEXT n ROWS ONLY.

Execution order (NOT the same as write order):

FROM — identify the source table.
WHERE — filter rows.
GROUP BY — group filtered rows.
HAVING — filter groups.
SELECT — compute the output columns.
ORDER BY — sort.
LIMIT/OFFSET — restrict count.

This matters for aliases: SELECT name AS n ... WHERE n = 'Alice' fails because WHERE runs before SELECT names the alias.

Code Example

sql

-- Basic SELECT with WHERE and ORDER BY
SELECT id, name, email, created_at
FROM users
WHERE active = 1
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;   -- page 3 (20 per page, 0-indexed)

-- Expressions in SELECT
SELECT
    id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    YEAR(created_at)                   AS signup_year,
    DATEDIFF(NOW(), created_at)        AS days_since_signup
FROM users;

-- NULL comparisons — use IS NULL, not = NULL
SELECT * FROM users WHERE deleted_at IS NULL;      -- active users
SELECT * FROM users WHERE deleted_at IS NOT NULL;  -- soft-deleted users

-- IN and BETWEEN
SELECT * FROM orders
WHERE status IN ('pending', 'processing')
  AND total BETWEEN 50.00 AND 500.00;

-- LIKE for pattern matching (% = any sequence, _ = any single char)
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE sku LIKE 'PHP-___';  -- exactly 3 chars after dash

-- PHP / Laravel Eloquent equivalents

php

<?php
// Eloquent equivalent of the SQL above
$users = DB::table('users')
    ->select('id', 'name', 'email', 'created_at')
    ->where('active', 1)
    ->where('created_at', '>=', '2024-01-01')
    ->orderByDesc('created_at')
    ->limit(20)
    ->offset(40)
    ->get();

// Eloquent model equivalent
$users = User::query()
    ->whereNull('deleted_at')
    ->whereIn('status', ['pending', 'processing'])
    ->whereBetween('total', [50, 500])
    ->orderBy('created_at', 'desc')
    ->paginate(20); // handles LIMIT/OFFSET automatically

PreviousMicroservices vs monolith — the tradeoffs for PHP appsArchitectural Patterns NextJOINs — INNER, LEFT, RIGHT, FULL, CROSS — visual mental modelSQL Deep Dive