{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"10-common-sql-mistakes-beginners-make-268ww","url":"https://zyvop.com/10-common-sql-mistakes-beginners-make-268ww","title":"10 Common SQL Mistakes Beginners Make","subtitle":"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.","tldr":"Some SQL mistakes throw errors; the dangerous ones quietly return wrong results. This guide walks through 10 common beginner mistakes, from comparing with NULL and mixing up COUNT(*) and COUNT(col) to missing GROUP BY columns and unsafe UPDATEs, each with a Mermaid diagram and a simple fix.","keywords":["beginners","NULL Handling","SQL","SQL Mistakes","query-optimization","Databases"],"entities":["Alysaa Lissy","beginners","NULL Handling","SQL","SQL Mistakes","query-optimization","Databases","ZyVOP"],"keyTakeaways":["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."],"headings":["1. Comparing with = NULL","2. Confusing COUNT(*) with COUNT(col)","3. Missing GROUP BY columns","4. Using WHERE when you need HAVING","5. Using a column alias in WHERE","6. Forgetting the join condition","7. Filtering a LEFT JOIN in WHERE","8. Using NOT IN with a subquery that can return NULL","9. Assuming rows come back in order","10. Running UPDATE or DELETE without WHERE","Quick Reference","Final Thoughts"],"outboundLinks":[],"contentText":"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 WHEREThe 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\"] --&gt; B[\"Comparison with NULL&lt;br/&gt;evaluates to UNKNOWN\"] B --&gt; C[\"WHERE keeps only TRUE\"] C --&gt; D[\"0 rows returned\"] E[\"WHERE phone IS NULL\"] --&gt; F[\"Evaluates to TRUE&lt;br/&gt;for NULL values\"] F --&gt; G[\"Matching rows returned\"]Expression Result NULL = NULL UNKNOWN NULL &lt;&gt; 5 UNKNOWN NULL IS NULL TRUE 5 IS NOT NULL TRUE Watch out: status &lt;&gt; '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:&lt;br/&gt;London, London, NULL, Paris\"] --&gt; A[\"COUNT(*) = 4&lt;br/&gt;every row\"] D --&gt; B[\"COUNT(city) = 3&lt;br/&gt;skips the NULL\"] D --&gt; C[\"COUNT(DISTINCT city) = 2&lt;br/&gt;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\"] --&gt; B{\"Inside an aggregate&lt;br/&gt;like SUM or COUNT?\"} B -- Yes --&gt; OK[\"Allowed\"] B -- No --&gt; C{\"Listed in GROUP BY?\"} C -- Yes --&gt; OK C -- No --&gt; ERR[\"Error in most databases&lt;br/&gt;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) &gt; 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) &gt; 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\"] --&gt; B[\"WHERE&lt;br/&gt;filters rows\"] B --&gt; C[\"GROUP BY&lt;br/&gt;builds groups\"] C --&gt; D[\"HAVING&lt;br/&gt;filters groups\"] D --&gt; E[\"SELECT&lt;br/&gt;aliases created\"] E --&gt; F[\"ORDER BY\"] F --&gt; 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 &gt; 100; -- Right: repeat the expression SELECT amount * 1.18 AS amount_with_tax FROM orders WHERE amount * 1.18 &gt; 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 &gt; 100;flowchart LR W[\"WHERE runs here&lt;br/&gt;alias not defined yet\"] --&gt; S[\"SELECT defines&lt;br/&gt;amount_with_tax\"] S --&gt; 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&lt;br/&gt;3 rows\"] --&gt; X[\"No join condition&lt;br/&gt;3 x 4 = 12 rows\"] O[\"orders&lt;br/&gt;4 rows\"] --&gt; X C --&gt; J[\"JOIN ON customer_id&lt;br/&gt;matching rows only&lt;br/&gt;4 rows, one per order\"] O --&gt; JWarning 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&lt;br/&gt;unmatched rows get NULL order columns\"] --&gt; B[\"WHERE o.status = 'completed'\"] B --&gt; C[\"NULL = 'completed' is UNKNOWN\"] C --&gt; D[\"Unmatched customers removed&lt;br/&gt;acts like INNER JOIN\"] A --&gt; E[\"Condition in ON instead\"] E --&gt; F[\"Orders filtered before joining&lt;br/&gt;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)\"] --&gt; B[\"id != 1 AND id != 2 AND id != NULL\"] B --&gt; C[\"id != NULL is UNKNOWN\"] C --&gt; D[\"TRUE AND TRUE AND UNKNOWN&lt;br/&gt;= UNKNOWN\"] D --&gt; 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&lt;br/&gt;no ORDER BY\"] --&gt; B[\"Any 3 rows&lt;br/&gt;can change between runs\"] C[\"ORDER BY amount DESC, order_id&lt;br/&gt;LIMIT 3\"] --&gt; 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\"] --&gt; B[\"2. Check the rows returned\"] B --&gt; C[\"3. Swap in UPDATE or DELETE\"] C --&gt; D[\"4. Run it inside BEGIN\"] D --&gt; E{\"Rows affected&lt;br/&gt;as expected?\"} E -- Yes --&gt; F[\"COMMIT\"] E -- No --&gt; G[\"ROLLBACK\"]BEGIN; -- START TRANSACTION in MySQL, BEGIN TRANSACTION in SQL Server DELETE FROM orders WHERE status = 'cancelled' AND order_date &lt; '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.","contentHash":"sha256:1c601e183a7e8a71b7e5cb56274d63412b8e51e737fa034ec1d1210277994085","authorName":"Alysaa Lissy","authorUrl":"https://zyvop.com/author/alysaa","authorSameAs":[],"category":"Databases","tags":["beginners","NULL Handling","SQL","SQL Mistakes","query-optimization"],"audience":"Developers, software engineers, and students learning Databases","tone":"Instructional, practical, code-first","readingTimeMinutes":7,"wordCount":1666,"faqs":null,"primaryTopic":"Databases","publishedAt":"2026-11-20T12:04:51.492Z","updatedAt":"2026-11-20T12:04:51.492Z","canonicalUrl":"https://zyvop.com/10-common-sql-mistakes-beginners-make-268ww"}