DatabaseMẹo hiểu SELECT: SQL không chạy theo thứ tự bạn viết, mà theo thứ tự thực thi:
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMITHệ quả quan trọng: alias đặt trong SELECT (vd price * 0.9 AS discounted) không dùng được trong WHERE vì lúc WHERE chạy thì SELECT chưa tính — nhưng dùng được trong ORDER BY (chạy sau SELECT).
Một query đầy đủ:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC LIMIT 20;Lưu ý hay bị hỏi: LIMIT mà không có ORDER BY cho kết quả không xác định (mỗi lần chạy ra hàng khác nhau).
Và đừng SELECT * ở production — lấy thừa dữ liệu, vỡ khi thêm cột, không tận dụng được covering index.
Key to understanding SELECT: SQL does not run in the order you write it, but in this execution order:
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMITImportant consequence: an alias defined in SELECT (e.g. price * 0.9 AS discounted) cannot be used in WHERE because WHERE runs before SELECT is computed — but it can be used in ORDER BY (which runs after SELECT).
A full query:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC LIMIT 20;Common interview pitfall: LIMIT without ORDER BY gives non-deterministic results (different rows each run).
And avoid SELECT * in production — it fetches extra data, breaks when columns are added, and can't use a covering index.