-- WRONG:
WHERE column = NULL
WHERE column <> NULL
-- CORRECT:
WHERE column IS NULL
WHERE column IS NOT NULL
example
-- This returns NO rows, even if phone is NULL:
SELECT * FROM users WHERE phone = NULL;
-- This works correctly:
SELECT * FROM users WHERE phone IS NULL;
-- This also fails silently:
SELECT * FROM users WHERE phone <> NULL;
-- Returns no rows because NULL <> NULL is UNKNOWN
output
-- NULL = NULL evaluates to UNKNOWN, not TRUE
-- NULL <> NULL also evaluates to UNKNOWN, not TRUE
Note NULL represents unknown. Any comparison with NULL yields UNKNOWN, which is treated as FALSE in WHERE. This includes =, <>, <, >, IN, and NOT IN. The only operators that work with NULL are IS NULL, IS NOT NULL, and IS DISTINCT FROM.
-- WRONG: selecting non-aggregated column without GROUP BY
SELECT user_id, first_name, COUNT(*)
FROM orders
JOIN users ON users.id = orders.user_id
GROUP BY user_id;
-- first_name is not in GROUP BY or an aggregate!
example
-- WRONG (MySQL without ONLY_FULL_GROUP_BY):
SELECT category_id, product_name, AVG(price)
FROM products
GROUP BY category_id;
-- Which product_name does it pick? Undefined!-- CORRECT:
SELECT category_id, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category_id;
output
-- PostgreSQL and standard SQL raise an error
-- MySQL may silently return an arbitrary value
Note Every column in SELECT must either be in GROUP BY or inside an aggregate function. MySQL's old default allowed this silently with random results. Always enable ONLY_FULL_GROUP_BY in MySQL. PostgreSQL enforces this strictly.
group by errornot in group byaggregate errorselect without group by
N+1 Query Problem
syntax
-- BAD: one query per user (in application code)-- for each user:-- SELECT * FROM orders WHERE user_id = ?-- GOOD: one query with JOIN
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
example
-- Instead of this application loop:-- users = query("SELECT * FROM users")-- for user in users:-- orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)-- Use a single query:
SELECT u.first_name, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id;
output
-- 1 query instead of N+1 queries
Note The N+1 problem is the most common performance killer in database-backed applications. Fetch related data in a single query using JOINs, or use your ORM's eager-loading feature. Signs: page load time grows linearly with the number of records.
n plus onen+1 querytoo many queriesquery loopeager loading
Implicit Type Conversion
syntax
-- WRONG: comparing string column to integer
WHERE phone_number = 2065551234-- The DB converts every phone_number to int for comparison!-- CORRECT: compare with matching types
WHERE phone_number = '2065551234'
example
-- Slow: index on user_code (VARCHAR) is not used
SELECT * FROM users WHERE user_code = 12345;
-- Fast: index is used
SELECT * FROM users WHERE user_code = '12345';
output
-- Type mismatch forces a full table scan because the index cannot be used
Note When you compare a string column to a number, the database may cast every row's string value to a number, which prevents index usage and can cause errors on non-numeric strings. Always match the data type in your comparison.
type conversionimplicit castindex not usedwrong type comparison
Missing Index on Foreign Keys
syntax
-- After creating a foreign key, add an index:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders (user_id);
example
-- Without index: this JOIN does a full table scan on orders
SELECT u.first_name, COUNT(*) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.first_name;
-- After adding index: uses index scan, much faster
CREATE INDEX idx_orders_user_id ON orders (user_id);
output
-- Foreign key columns need indexes for JOIN performance
Note PostgreSQL does NOT automatically create indexes on foreign key columns (only on primary keys). MySQL InnoDB DOES auto-create them. Always check. A missing FK index also slows down DELETE on the parent table because it must scan the child table for references.
-- AVOID in production:
SELECT * FROM users;
-- PREFER:
SELECT id, first_name, email FROM users;
example
-- Bad: returns all columns including large TEXT/BLOB
SELECT * FROM products;
-- Good: only what you need
SELECT id, product_name, price FROM products;
output
-- SELECT * fetches unnecessary data and breaks when schema changes
Note SELECT * wastes bandwidth on unused columns, prevents covering index optimizations, and breaks application code when columns are added, removed, or reordered. It is fine for ad-hoc exploration but should never appear in production queries or views.
select starselect all columnsavoid select star
UPDATE / DELETE Without WHERE
syntax
-- DANGEROUS:
UPDATE users SET is_active = false;
DELETE FROM orders;
-- SAFE: always filter
UPDATE users SET is_active = false WHERE last_login < '2024-01-01';
DELETE FROM orders WHERE status = 'cancelled';
example
-- Accidentally deactivates ALL users:
UPDATE users SET is_active = false;
-- What you meant:
UPDATE users SET is_active = false
WHERE last_login < '2024-01-01';
output
-- Without WHERE, every row in the table is affected
Note Always write the WHERE clause first, then add the UPDATE/DELETE around it. Or test with a SELECT using the same WHERE before running the modification. Wrap destructive operations in a transaction so you can ROLLBACK if the row count looks wrong.
update without wheredelete without whereaccidental update alldangerous delete
OR Conditions and Index Usage
syntax
-- Often slow (index may not be used):
WHERE city = 'Seattle' OR state = 'WA'-- Faster alternative:
SELECT ... WHERE city = 'Seattle'
UNION
SELECT ... WHERE state = 'WA';
example
-- May not use either index effectively:
SELECT * FROM users
WHERE email = 'alice@example.com'
OR phone = '2065551234';
-- Better with UNION (each query uses its own index):
SELECT * FROM users WHERE email = 'alice@example.com'
UNION
SELECT * FROM users WHERE phone = '2065551234';
output
-- UNION lets each branch use its optimal index
Note OR conditions on different columns often prevent the optimizer from using indexes efficiently. Rewriting as UNION (or UNION ALL if you know there are no duplicates) lets each branch use its own index. Check with EXPLAIN to verify.
or performanceor indexslow or queryunion vs or
BETWEEN with Timestamps
syntax
-- WRONG: misses times after midnight on end date
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'-- CORRECT: use exclusive upper bound
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'
example
-- Misses orders on Dec 31 after midnight:
SELECT * FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
-- created_at = '2025-12-31 14:30:00' is included-- But the pattern is fragile with timestamp precision-- Safe pattern:
SELECT * FROM orders
WHERE created_at >= '2025-01-01'
AND created_at < '2026-01-01';
output
-- The >= / < pattern correctly handles all times within the range
Note BETWEEN '2025-01-01' AND '2025-12-31' with timestamps works if the value is exactly midnight, but this pattern is fragile. The >= / < (half-open interval) approach is unambiguous and works regardless of timestamp precision.
between timestampdate range bugtimestamp rangebetween date mistake