--- title: "10 Common SQL Mistakes Beginners Make" author: "Alysaa Lissy" published: "2026-11-20T12:04:51.492Z" updated: "2026-11-20T12:04:51.492Z" url: "https://zyvop.com/10-common-sql-mistakes-beginners-make-268ww" canonical: "https://zyvop.com/10-common-sql-mistakes-beginners-make-268ww" tags: ["beginners","NULL Handling","SQL","SQL Mistakes","query-optimization"] --- # 10 Common SQL Mistakes Beginners Make Avoid the NULL traps, GROUP BY errors, and join bugs that trip up new SQL users, with simple diagrams and a quick fix for each mistake. ![Cover image]() Some beginner SQL mistakes throw an error you can fix in seconds. The more dangerous ones run fine and quietly return the wrong rows or numbers. This post covers ten of both kinds, each with a diagram showing why it happens and a fix you can apply immediately. ```mermaid mindmap root((10 SQL Mistakes)) NULL traps Comparing with = NULL COUNT star vs COUNT column NOT IN with NULLs Aggregation Missing GROUP BY columns WHERE vs HAVING Query logic Alias in WHERE Missing JOIN condition LEFT JOIN filtered in WHERE Safety No ORDER BY UPDATE or DELETE without WHERE ``` The examples use these two tables (PostgreSQL syntax): ```mermaid erDiagram CUSTOMERS ||--o{ ORDERS : places CUSTOMERS { int customer_id PK string first_name string phone string city } ORDERS { int order_id PK int customer_id FK date order_date decimal amount string status } ``` * * * ## 1\. Comparing with `= NULL` `NULL` means "unknown", so nothing is ever equal to it, not even another `NULL`. The comparison evaluates to `UNKNOWN`, and `WHERE` keeps only rows that evaluate to `TRUE`. ```sql -- Wrong: returns 0 rows SELECT * FROM customers WHERE phone = NULL; -- Right SELECT * FROM customers WHERE phone IS NULL; SELECT * FROM customers WHERE phone IS NOT NULL; ``` ```mermaid flowchart LR A["WHERE phone = NULL"] --> B["Comparison with NULL
evaluates to UNKNOWN"] B --> C["WHERE keeps only TRUE"] C --> D["0 rows returned"] E["WHERE phone IS NULL"] --> F["Evaluates to TRUE
for NULL values"] F --> G["Matching rows returned"] ``` | Expression | Result | | --- | --- | | `NULL = NULL` | UNKNOWN | | `NULL <> 5` | UNKNOWN | | `NULL IS NULL` | TRUE | | `5 IS NOT NULL` | TRUE | **Watch out:** `status <> 'completed'` also drops rows where `status` is `NULL`. Add `OR status IS NULL` if you want them. * * * ## 2\. Confusing `COUNT(*)` with `COUNT(col)` `COUNT(*)` counts rows. `COUNT(col)` counts rows where `col` is not `NULL`. `COUNT(DISTINCT col)` counts unique non-`NULL` values. ```sql SELECT COUNT(*) AS all_rows, COUNT(city) AS rows_with_city, COUNT(DISTINCT city) AS unique_cities FROM customers; ``` For a `city` column holding `London, London, NULL, Paris`: ```mermaid flowchart LR D["city column:
London, London, NULL, Paris"] --> A["COUNT(*) = 4
every row"] D --> B["COUNT(city) = 3
skips the NULL"] D --> C["COUNT(DISTINCT city) = 2
London, Paris"] ``` **Rule of thumb:** use `COUNT(*)` for "how many rows", and `COUNT(col)` only when you specifically want to skip missing values. * * * ## 3\. Missing `GROUP BY` columns Every column in `SELECT` must either be inside an aggregate function or appear in `GROUP BY`. (The main exception is a column that depends on a grouped primary key, which PostgreSQL and MySQL allow.) ```sql -- Wrong: status is neither aggregated nor grouped SELECT customer_id, status, SUM(amount) FROM orders GROUP BY customer_id; -- Fix A: group by it (one row per customer and status) SELECT customer_id, status, SUM(amount) FROM orders GROUP BY customer_id, status; -- Fix B: aggregate it (or remove it) SELECT customer_id, MAX(status), SUM(amount) FROM orders GROUP BY customer_id; ``` ```mermaid flowchart TD A["Each column in SELECT"] --> B{"Inside an aggregate
like SUM or COUNT?"} B -- Yes --> OK["Allowed"] B -- No --> C{"Listed in GROUP BY?"} C -- Yes --> OK C -- No --> ERR["Error in most databases
arbitrary value in a few"] ``` PostgreSQL, SQL Server, and Oracle raise an error. MySQL does too unless `ONLY_FULL_GROUP_BY` is disabled, and SQLite silently picks an arbitrary row's value, which is worse. * * * ## 4\. Using `WHERE` when you need `HAVING` `WHERE` filters rows _before_ grouping, so it can't see aggregates. `HAVING` filters groups _after_ aggregation. ```sql -- Wrong: error, aggregates aren't allowed in WHERE SELECT customer_id, SUM(amount) AS total FROM orders WHERE SUM(amount) > 1000 GROUP BY customer_id; -- Right: row filter in WHERE, group filter in HAVING SELECT customer_id, SUM(amount) AS total FROM orders WHERE status = 'completed' GROUP BY customer_id HAVING SUM(amount) > 1000; ``` This is the logical order in which SQL processes a query, and it explains this mistake and the next one: ```mermaid flowchart LR A["FROM / JOIN"] --> B["WHERE
filters rows"] B --> C["GROUP BY
builds groups"] C --> D["HAVING
filters groups"] D --> E["SELECT
aliases created"] E --> F["ORDER BY"] F --> G["LIMIT"] ``` * * * ## 5\. Using a column alias in `WHERE` Aliases are created in the `SELECT` step, which runs _after_ `WHERE`. Most databases therefore reject them there. ```sql -- Wrong: error, column "amount_with_tax" does not exist SELECT amount * 1.18 AS amount_with_tax FROM orders WHERE amount_with_tax > 100; -- Right: repeat the expression SELECT amount * 1.18 AS amount_with_tax FROM orders WHERE amount * 1.18 > 100; -- Or wrap it in a CTE WITH priced AS ( SELECT order_id, amount * 1.18 AS amount_with_tax FROM orders ) SELECT * FROM priced WHERE amount_with_tax > 100; ``` ```mermaid flowchart LR W["WHERE runs here
alias not defined yet"] --> S["SELECT defines
amount_with_tax"] S --> O["ORDER BY can use it"] ``` * * * ## 6\. Forgetting the join condition Without a condition linking the tables, every row from one table pairs with every row from the other (a Cartesian product). Two tables with 3 and 4 rows produce 12. ```sql -- Wrong: comma join with no condition SELECT c.first_name, o.order_id FROM customers c, orders o; -- Right SELECT c.first_name, o.order_id FROM customers c JOIN orders o ON o.customer_id = c.customer_id; ``` ```mermaid flowchart LR C["customers
3 rows"] --> X["No join condition
3 x 4 = 12 rows"] O["orders
4 rows"] --> X C --> J["JOIN ON customer_id
matching rows only
4 rows, one per order"] O --> J ``` **Warning sign:** the result has exactly `rows(A) x rows(B)` rows. * * * ## 7\. Filtering a `LEFT JOIN` in `WHERE` A `LEFT JOIN` keeps every left-side row and fills the right-side columns with `NULL` when nothing matches. A `WHERE` condition on a right-side column then removes those `NULL` rows, turning your `LEFT JOIN` into an `INNER JOIN`. ```sql -- Wrong: customers with no completed orders disappear SELECT c.first_name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'completed'; -- Right: put the condition in ON SELECT c.first_name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'completed'; ``` ```mermaid flowchart TD A["LEFT JOIN keeps all customers
unmatched rows get NULL order columns"] --> B["WHERE o.status = 'completed'"] B --> C["NULL = 'completed' is UNKNOWN"] C --> D["Unmatched customers removed
acts like INNER JOIN"] A --> E["Condition in ON instead"] E --> F["Orders filtered before joining
all customers kept"] ``` * * * ## 8\. Using `NOT IN` with a subquery that can return `NULL` If the list contains even one `NULL`, `NOT IN` never returns `TRUE`, so you get zero rows. ```sql -- Wrong: returns nothing if any orders.customer_id is NULL SELECT first_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders); -- Right: NOT EXISTS handles NULLs safely SELECT c.first_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); ``` ```mermaid flowchart TD A["id NOT IN (1, 2, NULL)"] --> B["id != 1 AND id != 2 AND id != NULL"] B --> C["id != NULL is UNKNOWN"] C --> D["TRUE AND TRUE AND UNKNOWN
= UNKNOWN"] D --> E["Row is never returned"] ``` Filtering with `WHERE customer_id IS NOT NULL` inside the subquery also works. * * * ## 9\. Assuming rows come back in order Without `ORDER BY`, SQL guarantees no particular order. Results may look sorted today and change after an update, an index change, or a new query plan. `LIMIT` without `ORDER BY` returns an arbitrary set of rows. ```sql -- Wrong: "top 3" is not defined SELECT * FROM orders LIMIT 3; -- Right: explicit order, plus a unique tie-breaker SELECT * FROM orders ORDER BY amount DESC, order_id LIMIT 3; ``` ```mermaid flowchart LR A["SELECT ... LIMIT 3
no ORDER BY"] --> B["Any 3 rows
can change between runs"] C["ORDER BY amount DESC, order_id
LIMIT 3"] --> D["Same 3 rows every time"] ``` The tie-breaker (`order_id`) matters: if two orders share the same `amount`, ordering by `amount` alone is still ambiguous. (`LIMIT` works in PostgreSQL and MySQL; SQL Server uses `TOP` or `FETCH FIRST`.) * * * ## 10\. Running `UPDATE` or `DELETE` without `WHERE` Without a `WHERE` clause, the statement affects every row in the table. ```sql -- Wrong: cancels every order UPDATE orders SET status = 'cancelled'; -- Right UPDATE orders SET status = 'cancelled' WHERE order_id = 1042; ``` A safer habit is to test the filter first, then run the change inside a transaction: ```mermaid flowchart TD A["1. Write a SELECT with your WHERE"] --> B["2. Check the rows returned"] B --> C["3. Swap in UPDATE or DELETE"] C --> D["4. Run it inside BEGIN"] D --> E{"Rows affected
as expected?"} E -- Yes --> F["COMMIT"] E -- No --> G["ROLLBACK"] ``` ```sql BEGIN; -- START TRANSACTION in MySQL, BEGIN TRANSACTION in SQL Server DELETE FROM orders WHERE status = 'cancelled' AND order_date < '2025-01-01'; -- check the reported row count, then: COMMIT; -- or ROLLBACK; ``` Many SQL clients run in auto-commit mode, so without an explicit `BEGIN` there is nothing to roll back. * * * ## Quick Reference | # | Mistake | Fix | | --- | --- | --- | | 1 | `= NULL` | Use `IS NULL` / `IS NOT NULL` | | 2 | `COUNT(*)` vs `COUNT(col)` | `*` counts rows; `col` skips `NULL`s | | 3 | Missing `GROUP BY` columns | Group by it or aggregate it | | 4 | Aggregate in `WHERE` | Use `HAVING` for group filters | | 5 | Alias in `WHERE` | Repeat the expression or use a CTE | | 6 | No join condition | Always add `ON` | | 7 | Right-table filter after `LEFT JOIN` | Move it into `ON` | | 8 | `NOT IN` with `NULL`s | Use `NOT EXISTS` | | 9 | No `ORDER BY` | Always sort, with a tie-breaker | | 10 | `UPDATE`/`DELETE` without `WHERE` | Test with `SELECT`, use a transaction | * * * ## Final Thoughts Six of these ten come down to two ideas: `NULL` doesn't behave like a normal value, and SQL runs your query in a different order than you write it. The other four are about being explicit: always state how tables join, how rows are grouped and sorted, and which rows a change applies to. Keep those habits and most of these bugs disappear before you hit Run.