iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

SELECT

By · Reviewed & last updated · Part of MySQL Tutorial

SELECT in MySQL works like every flavour of SQL, with a few quirks: ORDER BY can reference aliases, GROUP BY allows non-aggregated columns under default settings (legacy — disable!), LIMIT supports offset/count.

Real queries you will write

EXAMPLE
-- Basic
SELECT id, email FROM users WHERE active = 1;

-- Joins + aliases
SELECT u.email, COUNT(p.id) AS post_count
FROM   users u
LEFT   JOIN posts p ON p.user_id = u.id
WHERE  u.created_at >= NOW() - INTERVAL 7 DAY
GROUP  BY u.id, u.email
HAVING post_count > 0     -- alias usable in HAVING/ORDER BY in MySQL
ORDER  BY post_count DESC
LIMIT  20;

-- DISTINCT
SELECT DISTINCT country FROM users;

-- Pagination — page 4, 10 per page
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 30;

-- Subqueries
SELECT * FROM users
WHERE id IN (SELECT user_id FROM posts GROUP BY user_id HAVING COUNT(*) > 10);

-- CTEs (MySQL 8+)
WITH active_users AS (
    SELECT * FROM users WHERE last_login >= NOW() - INTERVAL 30 DAY
)
SELECT au.email, COUNT(p.id) AS post_count
FROM   active_users au
LEFT   JOIN posts p ON p.user_id = au.id
GROUP  BY au.id, au.email;

-- Always enable strict SQL modes in modern MySQL:
SET sql_mode = 'STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';

Why it matters

Add an index to columns you filter, sort, or JOIN on. Inspect with EXPLAIN: any row with type: ALL is a sequential scan you probably want to fix.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
SELECT id, name, created_at
FROM users
WHERE email LIKE '%@example.com'
ORDER BY created_at DESC
LIMIT 20;
Try it Yourself »

Discussion

Loading…