Databases

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.

·
6 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

10 Common SQL Mistakes Beginners Make

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.

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):

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.

-- 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;
flowchart LR
    A["WHERE phone = NULL"] --> B["Comparison with NULL<br/>evaluates to UNKNOWN"]
    B --> C["WHERE keeps only TRUE"]
    C --> D["0 rows returned"]
    E["WHERE phone IS NULL"] --> F["Evaluates to TRUE<br/>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.

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:

flowchart LR
    D["city column:<br/>London, London, NULL, Paris"] --> A["COUNT(*) = 4<br/>every row"]
    D --> B["COUNT(city) = 3<br/>skips the NULL"]
    D --> C["COUNT(DISTINCT city) = 2<br/>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.)

-- 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;
flowchart TD
    A["Each column in SELECT"] --> B{"Inside an aggregate<br/>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<br/>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.

-- 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:

flowchart LR
    A["FROM / JOIN"] --> B["WHERE<br/>filters rows"]
    B --> C["GROUP BY<br/>builds groups"]
    C --> D["HAVING<br/>filters groups"]
    D --> E["SELECT<br/>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.

-- 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;
flowchart LR
    W["WHERE runs here<br/>alias not defined yet"] --> S["SELECT defines<br/>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.

-- 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;
flowchart LR
    C["customers<br/>3 rows"] --> X["No join condition<br/>3 x 4 = 12 rows"]
    O["orders<br/>4 rows"] --> X
    C --> J["JOIN ON customer_id<br/>matching rows only<br/>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.

-- 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';
flowchart TD
    A["LEFT JOIN keeps all customers<br/>unmatched rows get NULL order columns"] --> B["WHERE o.status = 'completed'"]
    B --> C["NULL = 'completed' is UNKNOWN"]
    C --> D["Unmatched customers removed<br/>acts like INNER JOIN"]
    A --> E["Condition in ON instead"]
    E --> F["Orders filtered before joining<br/>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.

-- 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
);
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<br/>= 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.

-- 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;
flowchart LR
    A["SELECT ... LIMIT 3<br/>no ORDER BY"] --> B["Any 3 rows<br/>can change between runs"]
    C["ORDER BY amount DESC, order_id<br/>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.

-- 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:

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<br/>as expected?"}
    E -- Yes --> F["COMMIT"]
    E -- No --> G["ROLLBACK"]
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 NULLs

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 NULLs

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.

Comments(0)

Join the discussion by logging into your account.

No comments yet. Be the first to comment!

Alysaa Lissy

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Alysaa Lissy's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.