SELECT Queries Basics

Introduction

SELECT reads data from tables—the most common SQL statement. This chapter builds filters with WHERE, sorts with ORDER BY, paginates with LIMIT, removes duplicates with DISTINCT, and uses aliases for readable results.

Prerequisites

• Inserting Data
• Sample rows in users and posts inside blog_dev

Select Columns

All columns:

SELECT * FROM users;

Specific columns (prefer in production):

SELECT id, email, display_name FROM users;

Code explanation:

• SELECT * is fine for exploration; apps should list needed columns to save I/O

WHERE Filters

Comparison operators:

SELECT id, display_name FROM users WHERE id = 1;
 
SELECT title FROM posts WHERE user_id = 2;
 
SELECT * FROM posts WHERE published_at IS NOT NULL;

Logical combinations:

SELECT title, published_at
FROM posts
WHERE user_id = 1
  AND published_at IS NOT NULL;

SELECT email FROM users
WHERE display_name = 'Ada Lo' OR display_name = 'Bob Chen';

Negation:

SELECT title FROM posts WHERE published_at IS NULL;
-- same idea:
SELECT title FROM posts WHERE NOT published_at IS NOT NULL;

BETWEEN, IN, LIKE

Range:

SELECT title, published_at
FROM posts
WHERE published_at BETWEEN '2026-05-01' AND '2026-05-31';

Set membership:

SELECT display_name FROM users
WHERE id IN (1, 2, 3);

Pattern match:

SELECT title FROM posts WHERE title LIKE 'Hello%';
 
SELECT email FROM users WHERE email LIKE '%@example.com';

Code explanation:

• % matches any length; _ matches one character
• LIKE may not use index on leading %—fine for small tables

IS NULL

SELECT id, title FROM posts WHERE published_at IS NULL;

Use IS NULL / IS NOT NULL—not = NULL (always unknown in SQL).

ORDER BY

Single column:

SELECT id, title FROM posts ORDER BY title ASC;

Newest first:

SELECT id, title, published_at
FROM posts
ORDER BY published_at DESC;

Multiple sort keys:

SELECT user_id, title, published_at
FROM posts
ORDER BY user_id ASC, published_at DESC;

LIMIT and Pagination

First two rows:

SELECT id, title FROM posts ORDER BY id LIMIT 2;

Page 2 with page size 2 (offset 2, take 2):

SELECT id, title FROM posts
ORDER BY id
LIMIT 2 OFFSET 2;

Legacy syntax (same meaning):

SELECT id, title FROM posts ORDER BY id LIMIT 2, 2;

Code explanation:

• Always pair LIMIT with stable ORDER BY—otherwise page contents shuffle
• Offset pagination slows on huge offsets—keyset pagination advanced

DISTINCT

Unique user ids who have posts:

SELECT DISTINCT user_id FROM posts;

Unique emails (already unique by constraint):

SELECT DISTINCT email FROM users;

COUNT(DISTINCT col) appears in aggregates chapter.

Column and Table Aliases

SELECT
  u.id AS user_id,
  u.display_name AS name,
  p.title AS post_title
FROM users AS u
JOIN posts AS p ON p.user_id = u.id;

Code explanation:

• AS optional—u display_name name works but AS improves clarity
• Short table aliases reduce typing in joins

Practical Queries

Published posts with author name (intro to join syntax—details next chapter):

SELECT
  p.id,
  p.title,
  u.display_name AS author
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p.published_at IS NOT NULL
ORDER BY p.published_at DESC
LIMIT 10;

Draft count per user preview:

SELECT user_id, COUNT(*) AS draft_count
FROM posts
WHERE published_at IS NULL
GROUP BY user_id;

Full GROUP BY in Group By and Aggregates.

FAQ

SELECT without FROM?

Valid in MySQL: SELECT 1 + 1; — calculator mode.

Case sensitive LIKE?

Depends on collation—utf8mb4_unicode_ci usually case-insensitive for letters.

Why ORDER BY before LIMIT?

Sort first, then cut—order of clauses in SQL text differs from execution logic but write in standard order: SELECT … FROM … WHERE … GROUP BY … H ORDER BY … LIMIT.

Safe dynamic WHERE?

Never concatenate user input—use prepared statements in app code.

Select for update?

Locking read in transactions—chapter 17.

Explain query plan?

EXPLAIN SELECT … — index chapter 16.