SQL Discussion, Troubleshooting & Common Engineering Traps

Even experienced software developers encounter non-intuitive SQL bugs, collation mismatches, and execution plan anomalies. In this concluding troubleshooting masterclass, we analyze the top 5 most common SQL mistakes, provide step-by-step diagnostic fixes, and summarize the complete learning roadmap.

The 5 Most Common SQL Gotchas & How to Fix Them

1. The "WHERE column = NULL" Trap

Symptom: Query executes without syntax error, but returns zero rows even though rows with NULL exist.

-- INCORRECT:
SELECT * FROM students WHERE phone_number = NULL;

-- CORRECT FIX:
SELECT * FROM students WHERE phone_number IS NULL;

2. The Accidental Cartesian Product (Missing JOIN ON)

Symptom: Query returns millions of duplicate rows and runs out of server memory.

-- INCORRECT (Missing ON clause creates Cartesian product M * N!):
SELECT * FROM students, departments;

-- CORRECT FIX:
SELECT * FROM students s 
JOIN departments d ON s.dept_id = d.dept_id;

3. Using Column Aliases in the WHERE Clause

Symptom: Error 1054: Unknown column 'total_score' in 'where clause'.

-- INCORRECT:
SELECT first_name, (score1 + score2) AS total_score
FROM exams
WHERE total_score > 150;

-- CORRECT FIX: Repeat expression or wrap in CTE
SELECT first_name, (score1 + score2) AS total_score
FROM exams
WHERE (score1 + score2) > 150;

4. The NOT IN with NULL Trap

Symptom: WHERE id NOT IN (SELECT foreign_id FROM ...) returns zero rows unexpectedly.

Reason: If the subquery contains even a single NULL value, the entire NOT IN evaluates to UNKNOWN. Always add WHERE foreign_id IS NOT NULL inside the subquery, or use NOT EXISTS.

Summary: Your Journey from Beginner to SQL Master

Congratulations! You have completed the comprehensive 52-Class SQL Masterclass on Veewom. You now command:

• Foundational Theory: RDBMS concepts, Edgar Codd rules, ACID transaction properties, and normalization.

• Schema Design (DDL): Creating databases, tables, constraints, primary keys, and foreign keys.

• Data Manipulation (DML): Inserting, projecting, updating, filtering, and sorting records.

• Relational Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF Joins.

• Senior Engineering Capabilities: Window functions, recursive CTEs, B-Tree index optimization, transaction isolation levels, stored procedures, and triggers.

Return to the SQL Tutorial Home to review any module or class anytime!