These ten mistakes trip up almost everyone learning SQL, and plenty of experienced developers too. Each one below shows the tempting-but-wrong version, the fix, and why it matters.
The ten mistakes
1. Comparing to NULL with =
In SQL, NULL means "unknown", so anything = NULL is never true, not even NULL = NULL. A filter like WHERE shipped_at = NULL silently returns zero rows.
SELECT * FROM orders WHERE shipped_at = NULL;SELECT * FROM orders WHERE shipped_at IS NULL;2. UPDATE / DELETE with no WHERE
Without a WHERE clause, these commands hit every row. One missing line can wipe a table. Always write the WHERE first, and run it as a SELECT to confirm the target rows.
UPDATE users SET is_active = 0; -- deactivates EVERYONE-- 1) check
SELECT id FROM users WHERE last_login < '2024-01-01';
-- 2) then act on the same filter
UPDATE users SET is_active = 0 WHERE last_login < '2024-01-01';3. SELECT * in production queries
SELECT * is fine while exploring, but in application code it pulls columns you don't need, breaks when the schema changes, and prevents covering-index optimisations. Name the columns you actually use.
SELECT * FROM customers;SELECT id, name, email FROM customers;4. GROUP BY with stray columns
Every column in the SELECT must either be inside an aggregate function or listed in GROUP BY. Otherwise the value is ambiguous, some databases guess, others reject it.
SELECT country, city, COUNT(*)
FROM customers
GROUP BY country; -- city is neither grouped nor aggregatedSELECT country, city, COUNT(*)
FROM customers
GROUP BY country, city;5. Assuming row order without ORDER BY
Rows come back in whatever order is convenient for the engine, and that can change between runs, versions, or indexes. If order matters, say so explicitly with ORDER BY.
SELECT name, score FROM players ORDER BY score DESC;6. INNER JOIN when you meant LEFT
An INNER JOIN drops rows that have no match. Counting customers by their orders with an INNER JOIN silently hides customers who never ordered. Use LEFT JOIN to keep them.
SELECT c.name, COUNT(o.id)
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;7. Wrapping indexed columns in functions
Applying a function to a column in WHERE stops the database using its index - it must compute the function for every row (a full scan). Rewrite the condition to leave the column bare.
WHERE YEAR(created_at) = 2026WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01'8. COUNT(column) vs COUNT(*)
COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL. Mixing them up produces subtly wrong totals.
COUNT(*) -- all rows
COUNT(phone) -- only rows that have a phone
COUNT(DISTINCT country) -- unique non-null countries9. Building queries by string concatenation
Gluing user input straight into SQL invites SQL injection. Always use parameterised queries / prepared statements so input is treated as data, never code.
// PHP — never do this
$sql = "SELECT * FROM users WHERE email = '" . $_GET['email'] . "'";// bound parameter
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$_GET['email']]);10. Integer division surprises
Dividing two integers often gives an integer result, truncating the fraction. Cast one side to a decimal to get the answer you expect.
SELECT 3 / 2; -- 1 in many databasesSELECT 3.0 / 2; -- 1.5
SELECT passed * 100.0 / total AS pass_rate;The habit that prevents most of these: read your query out loud before running it, and test destructive statements as a SELECT first.