# 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.

![Mermaid Diagram](https://mermaid.ink/img/pako:eNpdUU1r4zAQ_StTnVpQYAt7CuzBaZwmxbFbf3Qp5KLYY2eopDGS0m625L8vjpOw9PjevI8Z5kvU3KCYCkO2MarfWADHHG5v739A8ZLAmnxQ7-jv7oYRQFolCQSnej9igAc2vXJkO_iksINfJ8l1mFVpCT4oBx_-jGrWe2MvijQrYZWO3sF5zo26zmGnAvFVuSbvh5rHPKueYfZ2Drou8nsZ5_FQs4xeV-njSL_s0R1Ac0f1RRdpUh7Ijobv6U_ZKoWabUP_dyfxohxHLemADptvAYVqMRyuRzFk-TzOYfZ2oarneVTGwA7mcRKX8eli3ocxREhh0BlFjZh-ibBDMzylwVbtdRByZF6VI7XV6AdNyzYslCF9EFMxUX2vceIPPqCRMNNk39eqLk54wTZI2IgCO0aoVhshIectB5awRP2BgWolIXKktASvrJ94dNQKeSop6O-wy_3P_o84HqXYdg-s2YmpuPncUUBx_AfSJrSx?type=png)

The examples use these two tables (PostgreSQL syntax):

![Mermaid Diagram](https://mermaid.ink/img/pako:eNplUMtOw0AM_BXj8-aAxCk3aKlAqCpqKKdKlZu4rcU-ol0HKGn-HaWpBGp8sWfG1ozcYhkqxhw5ToX2kdzaAwBMVsXbYv64LOB0yrLQwmI57VEOtaWS0_VWOxB9iVcom6TBcdxIBa8vf1rSKH4PO4lJN54cj6T6EPyYLUWPA9kN7RLnyjbEaux5nWf2T6tI-XLVj_8ELsWRBXKh8TrKk5S0uTyhQ4OOoyOpMG9RD-z6h1a8o8YqmoF5pyi0tZz6nV3wOiMn9og5ZlTXlrN0TMrOwIMV_zGnsjjjWfBqYI0F7wPD6nmNBpZhGzQYeGL7ySolGbiPQtZAIp-yxFF2aM4mhfz0WW7v6m_sOoPb_STYEDHHm6-DKGP3C58woW8?type=png)

* * *

## 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 Diagram](https://mermaid.ink/img/pako:eNplkE1vwjAMhv-K53OqbdJOaEMCVgYaMAnGODQcQuvSiDSukkDHEP99apHQPo62H79-5BOmnBF2MDdcp4VyASZzaQEAeonE1Siex1AVbAmeYLacTCSuIYq60E8kDrislNOeLdQ6FO38ceNuu3RQZq8CeQgMy9nr7G01k7i-5Pbb_cE1fUdUeWBrjvA-X8ZXbtByz4nEO3Bce3AU9s5SdiXiP4bjxS_FYSIx_mnSxLd-ObuWhGZK_ho4bPdeEolTFdJC2-2_yyiwJFcqnWHnhKGgsvleRrnam4Di0vlQTquNId8wOdswVKU2R-xgpKrKUOSPPlApoG-03U1VumjrIdsgQOKCtkywHEsUMOcNBxYwInOgoFMloOe0MgK8sj7y5HSOoj2y0F-Ny_1D9Ynns8DNdsCGHXbwpi50IDx_A8mjnc4?type=png)

| Expression | Result |
| --- | --- |
| NULL = NULL | UNKNOWN |
| NULL &lt;&gt; 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 Diagram](https://mermaid.ink/img/pako:eNpdj9FOwjAUhl_leK7UdDEqV4uSwAiRZKJhwxvKRbedsYauXdoCTsK7m00h0avmNN93_v8cMTcFYYilMoe8EtZDvOAaAGCy4phL30Ju1K7W4VNm74ax0YXRDM7vfBnHDN6FlY7jGoJgCKMVx-htOU-vb2_gGQa9R3uyLVhz4Lj-Xd_D4wvcRXX8Y8-7rWwc-Ir6hH9SdJEmsySdzaMUzvbDn5bnXsiwJlsLWWB4RF9R3Z1cUCl2yiP7-fkQVopMkeuY0mg_FbVULYYYiKZRFLjWeaoZjJXU21eRJ_08Ndoz4JjQxhAsZxwZLExmvGHwQmpPXuaCwchKoRg4oV3gyMoSWR-SyK-uy_2g-cTTiWG2iYwyFkO8OlTSE56-ASkphKE?type=png)

**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 Diagram](https://mermaid.ink/img/pako:eNpVkN1qwkAQRl9lOtcbSqFXUiyaxlbqTzFaENeLSTKJi5td2V21Vnz3kliovZyPw5lv5oy5LRg7WGp7zDfkAsxfpAEA6K0kJpRvILd6XxtQBtJklMRziWuIoi70zxKHxquCgQxQVTmuKPBT5u67Wm0Z0sUYrIN4upjMnyVert4-RBEs2beO6ftKYk9re-RC4vqGmNgWiM8SR8oHLpoCr7Pp4gP6yz9b_N92E_4KktmsOcQ56xpDbX2AggJl5Nm3XcllKjhyJziQ3nNDEZR8lLhGgTW7mlSBnTOGDdfNrwouaa8DimvySU5Rptk3TGlNGFCt9Ak7GNFupznyJx-4FtDXymzHlKftPLAmCJCYcmUZFkOJAmY2s8EKeGN94KByEtBzirQAT8ZHnp0qUbRLUvXddHl43H3h5SIwq2KrrcMO3h03KjBefgDh8pY0?type=png)

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 Diagram](https://mermaid.ink/img/pako:eNpd0F9rwjAQAPCvcrvnFBnsSYZgtdUOtaPqhjQ-pO1Vw9KmJHHOid99GMGxPd7d7_5wZyx1RdjHWuljuRfGwSzjLQDAMOcYZ-kcevCSJguOWwiCAYQ5x_dplEXPhekNaqkcGQtGHy3H7a0z9HCUc5xk6foVwo23xUGqysLO6EP3i0cej3OO0-Fbspj8GfvPjr2Nco7LaBaNVt4KJYUlC6Uh4ai648jjOOeYZuMog3BzL8W-NMk5zpJ5suK4RYYNmUbICvtndHtqrk-pqBYH5ZDdMm_CSFEosldT69bFopHqhH0MRNcpCuzJOmoYhEq2H3NRLn0c69Yx4LiknSZYJxwZZLrQTjOYkvokJ0vBYGikUAysaG1gycgamV-ylN_XWx6fui-8XBgWu5FW2mAfH4576QgvP_jIjLc?type=png)

* * *

## 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 Diagram](https://mermaid.ink/img/pako:eNotTttqwkAQ_ZXpPG8ohT6FIlQbsWARkrZSXJFJMjFLN7uyO6layb-XaB_P_Vyw8jVjio31x6qlILDMtQMAWG80rhdZnkHoXYSWAz-V4X5C1lAE5wVqbozjGs4sGreQJBMoNhqLbJnN3v_VeMt0vneyOxppd0InjdvbRnENrTYaV_lLlsP0Cypy0EcGM3aiwo5DR6bG9ILScjd-rbmh3gqqG_NJwVBpOY6exjuZU2fsGVNM6HCwnMRzFO4UTK1x329UFVc8904UaCx47xk-XjUqyH3pxStYsP1hMRUpeA6GrIJILiaRg2lQXUcK8zt-eXg8nHAYFJb7mbc-YIp3x9YI4_AHxPhzDQ?type=png)

* * *

## 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 Diagram](https://mermaid.ink/img/pako:eNpVj1FLAzEQhP_Kus85pNqnQwtaKLZoCy2KcBHJ5fZ60SR7JKltLf3v0muL-jjLzDezO9RcEeZYW17rRoUEj3PpAQCGhUS9iokdhXhThsvBNQReR4lvkGUDeC0kThk-2HjQ7CuTDPuTbwN9uIXe1TlwJM4KiRyqM67_H3dq7cSkkDiZjacwm8J5w7upuphTSTfGL7s0sLfbPzQB7AlaCtAV_VYfsSjQUXDKVJjvMDXkDr9XVKuVTSiOlxcVjCotxYOnZp9Gyhm7xRwz1baWsriNiZyAe2v855PSi06P2CcBEhe0ZILnsUQBcy45sYAHsl-UjFYC7oJRVkBUPmaRgqlRdCUL833Y0uu3G9zvBZbLIVsOmOPFujGJcP8DTEGKqw?type=png)

**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 Diagram](https://mermaid.ink/img/pako:eNpVkU1v2kAQhv_KdC65rFtV6gm1kYAYhZYaCUJz8HJYe8ewZb1j7Y5D04j_HmEkqz3O6NH7MfOGNVvCCTaez_XRRIGnBx0AAKalxlW-eILv62UBJ6IugfEe6j4JtxTT1yp-uu9Da6Q-koXI5wQHEih2qxVwtBShZt-3IWncQ5bdw6zU-PyYb3Lgj0mM9Am-wV3NbedJyN5p3N-8ZwM-LzUOYv9B4BLsih_F-rkY-fnAP5Qad2OeMSdEavmF7JDX1JLAuxPBsijyzVBulJkOMnmpcc7BOnEcwAVYF-BCEjJ2JPOBXJQa19eiCRrnhSJZqKjhSPCbXXDhcPP892pwok407lFhS7E1zuLkDeVI7fUNlhrTe0F12_wy0ZnKU7oyDQdZmNb5V5xgZrrOU5Zek1CrYOZdOP009XaYFxxEgcYtHZhgt9SoYMMVCyt4JP9C4mqjYBqd8QqSCSlLFF2DajDZur_XLJ-_dH_wclFYHebsOeIEP5yPTggv7_kDuYs?type=png)

* * *

## 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 Diagram](https://mermaid.ink/img/pako:eNplj0FvwjAMhf-K8WmTUk2gnaqBBBQ0NBYkoNuBcEhbF6KlCUoCjE3896nthCbt9mx_79n-xtwWhDGW2p7zvXQB1okwAADDjUBVAF-sYcbhrsugx4Cn8_m9wC1E0QBGLdHpQxeGPIFW9_7oGhe4bQNHjWl8M9VDUB5S_sIX7_zGjRsu2QhcL9NJk3YTv-xT5h4G_X_OpHFONgKX9lxHGzqRA0fh6AwVArfIsCJXSVVg_I1hT1X9fEGlPOqArO28SadkpsnXTGlNmMpK6QvGGMnDQVPkLz5QxWCklfl4lfmqqafWBAYCV7SzBOlMIIOlzWywDJ5JnyioXDIYOiU1Ay-Njzw5VSJrlqzUV31L9_Hwidcrw2w3tto6jLFz3qtAeP0BJyuAhg?type=png)

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 Diagram](https://mermaid.ink/img/pako:eNo1j91qwkAUhF_l9FxvLMVehSJojFRQCoktFFdkk5yYpfsju6tpKr57SdTLGYb5Zi5Y2oowxlrZtmyEC7DKuAEAmG455ukqTTYwGo1gtVwvNzB-K9zzxFj4yOZpBrNvjjuIognMthynpoMxONv6IVUKA2UjzIGgoNASGXAn4znuboBky_FRA0LbkwkwT_OEgXUVub2shpo7-AGa97OEpjsJ6EyugyA1cdwhQ01OC1lhfMHQkO6_VVSLkwrIbs6XcFIUinyfqa0JC6Gl6jDGSByPiiLf-UCawUxJ87MWZT7ohTWBAcecDpbgc8mRQWYLGyyDd1JnCrIUDKZOCsXAC-MjT07WyAZILv_6LS-vx1-8XhkWh8Qq6zDGp7aRgfD6D4Vefvs?type=png)

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 Diagram](https://mermaid.ink/img/pako:eNo9kMFu2zAQRH9ls2fKRZqcjCKFJdGJUTspJKVBIfpAS6uICEUKJF3FMfzvhWnAx9l9M7PYIza2JZxjp-3U9NIFqHJhAAAWtcDbGbw5FQgklHzNswomFXo42L2DtydecIFbSJIHSGuB32eQ9dR8QOgJnJ08OAp7Z6gVuL1kphHOaoF3MygnOYIy8Po7X1QcrIOcr3nFr3QW6bwWeD-DYm9ABVDGq5Yg5Y-r5yuYR5AfBRbnWtl11ARqf-zctwfpgT7HqH8KPF0MHJIE_pKPvmUtMHvZbFbVNTDun21cP9YCi5f1Ol1kvwRukeFAbpCqxfkRQ0_D-X0tdXKvA7LL5I90Su40-TPTWROWclD6gHNM5DhqSvzBBxoYpFqZj41syqiX1gQGAkt6twSvK4EMCruzwTJ4Iv2Pgmokg4VTUjPw0vjEk1MdslhSqq_zLbf34yeeTgx375nV1uEcb6ZeBcLTf8c2l9A?type=png)

```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 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.

---

*Published via [ZyVOP](https://zyvop.com/10-common-sql-mistakes-beginners-make-268ww?utm_source=hashnode&utm_medium=crosspost&utm_campaign=syndication) — Write once in Markdown, auto-backup to GitHub, and syndicate to Dev.to, Medium & Hashnode in 1 click.*
