Chapter 2: Basic Queries

1 SELECT Basics

Selecting Columns

The SELECT statement is the foundation of SQL queries. You can retrieve all columns or specific ones.

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with expressions
SELECT name, age, age * 12 AS age_in_months FROM users;

Column Aliases

Use AS to rename columns in the output. Aliases make results more readable and are essential for computed columns.

-- Using AS for aliases
SELECT
    name AS user_name,
    email AS contact_email,
    COALESCE(age, 0) AS user_age
FROM users;

-- Aliases with spaces (use backticks or double quotes)
SELECT
    name AS `Full Name`,
    email AS `Email Address`
FROM users;

DISTINCT

Remove duplicate rows from the result set with DISTINCT.

-- Get unique ages
SELECT DISTINCT age FROM users;

-- DISTINCT on multiple columns (unique combinations)
SELECT DISTINCT age, name FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT age) AS unique_ages FROM users;

2 WHERE Filtering

Comparison Operators

The WHERE clause filters rows based on conditions. MySQL supports all standard comparison operators.

-- Equal
SELECT * FROM users WHERE age = 28;

-- Not equal
SELECT * FROM users WHERE age != 28;
SELECT * FROM users WHERE age <> 28;  -- alternative syntax

-- Greater than, less than
SELECT * FROM users WHERE age > 30;
SELECT * FROM users WHERE age <= 25;

-- Greater than or equal
SELECT * FROM users WHERE age >= 25;

Logical Operators: AND / OR

Combine multiple conditions with AND and OR. Use parentheses to control evaluation order.

-- AND: both conditions must be true
SELECT * FROM users WHERE age > 25 AND age < 35;

-- OR: at least one condition must be true
SELECT * FROM users WHERE name = 'Alice Johnson' OR name = 'Bob Smith';

-- Combining AND/OR with parentheses
SELECT * FROM users
WHERE (age > 25 AND age < 35)
   OR name = 'Charlie Lee';

IN Operator

The IN operator is shorthand for multiple OR conditions on the same column.

-- Check if value is in a set
SELECT * FROM users WHERE age IN (22, 28, 35);

-- Equivalent to:
SELECT * FROM users WHERE age = 22 OR age = 28 OR age = 35;

-- NOT IN: exclude values
SELECT * FROM users WHERE age NOT IN (22, 28);

BETWEEN

The BETWEEN operator selects values within an inclusive range.

-- Age between 25 and 35 (inclusive)
SELECT * FROM users WHERE age BETWEEN 25 AND 35;

-- Equivalent to:
SELECT * FROM users WHERE age >= 25 AND age <= 35;

-- Date ranges
SELECT * FROM users
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

LIKE Pattern Matching

Use LIKE for pattern matching with wildcards: % (any characters) and _ (single character).

-- Names starting with 'A'
SELECT * FROM users WHERE name LIKE 'A%';

-- Names ending with 'son'
SELECT * FROM users WHERE name LIKE '%son';

-- Names containing 'li'
SELECT * FROM users WHERE name LIKE '%li%';

-- Email with exactly 3 characters before @
SELECT * FROM users WHERE email LIKE '___@%';

-- Case-insensitive by default in MySQL (depends on collation)
SELECT * FROM users WHERE name LIKE 'alice%';

IS NULL / IS NOT NULL

NULL represents missing or unknown data. You cannot compare NULL with =; use IS NULL instead.

-- Find users with no age specified
SELECT * FROM users WHERE age IS NULL;

-- Find users with age specified
SELECT * FROM users WHERE age IS NOT NULL;

-- WRONG: this will NOT work as expected
-- SELECT * FROM users WHERE age = NULL;  -- always returns empty!

3 ORDER BY Sorting

Ascending and Descending

Use ORDER BY to sort results. ASC (ascending) is the default; use DESC for descending order.

-- Sort by age ascending (default)
SELECT * FROM users ORDER BY age ASC;

-- Sort by age descending
SELECT * FROM users ORDER BY age DESC;

-- Sort by name alphabetically
SELECT * FROM users ORDER BY name;

Multi-Column Sorting

Sort by multiple columns to break ties. Each column can have its own sort direction.

-- Sort by age descending, then name ascending
SELECT * FROM users ORDER BY age DESC, name ASC;

-- Sort by expression
SELECT name, age, COALESCE(age, 0) AS effective_age
FROM users
ORDER BY effective_age DESC;

NULL Ordering

In MySQL, NULL values appear first when sorting ASC and last when sorting DESC.

-- NULLs appear first with ASC
SELECT name, age FROM users ORDER BY age ASC;
-- Eve Brown  | NULL
-- Charlie    | 22
-- Alice      | 28
-- Diana      | 31
-- Bob        | 35

-- NULLs appear last with DESC
SELECT name, age FROM users ORDER BY age DESC;
-- Bob        | 35
-- Diana      | 31
-- Alice      | 28
-- Charlie    | 22
-- Eve Brown  | NULL

-- Force NULLs to the end with ASC
SELECT name, age FROM users
ORDER BY age IS NULL ASC, age ASC;

4 LIMIT Pagination

Basic LIMIT

Use LIMIT to restrict the number of rows returned. Essential for pagination and previewing data.

-- Get first 3 rows
SELECT * FROM users LIMIT 3;

-- Get first 2 oldest users
SELECT * FROM users ORDER BY age DESC LIMIT 2;

LIMIT with OFFSET

Combine LIMIT with OFFSET to implement pagination. OFFSET specifies how many rows to skip.

-- Page 1: first 2 rows
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 0;

-- Page 2: next 2 rows
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 2;

-- Page 3: next 2 rows
SELECT * FROM users ORDER BY id LIMIT 2 OFFSET 4;

-- Alternative syntax: LIMIT offset, count
SELECT * FROM users ORDER BY id LIMIT 2, 2;  -- same as LIMIT 2 OFFSET 2

Pagination in Different Databases

5 Common Functions

String Functions

MySQL provides a rich set of string manipulation functions.

-- Convert to uppercase / lowercase
SELECT UPPER(name), LOWER(email) FROM users;

-- String length
SELECT name, CHAR_LENGTH(name) AS name_length FROM users;

-- Concatenation
SELECT CONCAT(name, ' <', email, '>') AS display FROM users;

-- Substring extraction
SELECT SUBSTRING(email, 1, INSTR(email, '@') - 1) AS username FROM users;

-- Trim whitespace
SELECT TRIM('  hello  ') AS trimmed;  -- 'hello'

-- Replace
SELECT REPLACE(email, '@example.com', '@company.com') AS new_email FROM users;

-- Pad strings
SELECT LPAD(id, 5, '0') AS padded_id FROM users;  -- '00001', '00002'

Number Functions

Common mathematical functions for numeric operations.

-- Rounding
SELECT ROUND(3.14159, 2);     -- 3.14
SELECT CEIL(3.2);             -- 4
SELECT FLOOR(3.8);            -- 3

-- Absolute value
SELECT ABS(-42);              -- 42

-- Modulo (remainder)
SELECT MOD(17, 5);            -- 2
SELECT 17 % 5;                -- 2

-- Power and square root
SELECT POWER(2, 10);          -- 1024
SELECT SQRT(144);             -- 12

-- Random number (0 to 1)
SELECT RAND();

-- Random integer between 1 and 100
SELECT FLOOR(RAND() * 100) + 1;

Date Functions

Date and time functions for temporal data processing.

-- Current date and time
SELECT NOW();                              -- 2025-04-16 14:30:00
SELECT CURDATE();                          -- 2025-04-16
SELECT CURTIME();                          -- 14:30:00

-- Extract parts from a date
SELECT
    YEAR(created_at)   AS yr,
    MONTH(created_at)  AS mo,
    DAY(created_at)    AS dy,
    HOUR(created_at)   AS hr
FROM users;

-- Date arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);    -- add 7 days
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);  -- subtract 1 month

-- Difference between dates
SELECT DATEDIFF('2025-12-31', '2025-01-01');  -- 364

-- Format dates
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');  -- 2025-04-16 14:30
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');   -- Wednesday, April 16, 2025

Conditional Functions

Conditional logic within SQL queries using IF, CASE, and COALESCE.

-- IF function
SELECT name, IF(age >= 30, 'Senior', 'Junior') AS category FROM users;

-- CASE expression
SELECT name, age,
    CASE
        WHEN age IS NULL THEN 'Unknown'
        WHEN age < 25 THEN 'Young'
        WHEN age < 35 THEN 'Mid-career'
        ELSE 'Experienced'
    END AS age_group
FROM users;

-- COALESCE: return first non-NULL value
SELECT name, COALESCE(age, 0) AS age_or_default FROM users;

-- NULLIF: return NULL if two values are equal
SELECT NULLIF(age, 0) AS age_or_null FROM users;

-- IFNULL: MySQL-specific alternative to COALESCE (for 2 args)
SELECT name, IFNULL(age, 0) AS age_or_default FROM users;

6 Chapter Summary