Typical Mistakes When Working with Subqueries

Working with subqueries is kinda like playing strip chess: everything seems simple at first, until you make a move with a mistake. So where do mistakes come from? Usually from misunderstanding the syntax, ignoring SQL logic quirks, or just not paying enough attention. In this lecture, we're gonna talk about the most common mistakes and how to dodge them.

Syntax Mistakes

Subqueries need you to be super careful with syntax. Missing commas, parentheses, or aliases can totally break your query. Let's check out some typical problems.

Missing Parentheses

Parentheses are a big deal when you're using subqueries. A subquery goes inside round brackets, and missing even one pair can cause a syntax error.

Example of a mistake:

SELECT student_name
FROM students
WHERE student_id IN SELECT student_id FROM enrollments);

Error:

ERROR:  syntax error at or near "SELECT"

Fix:

SELECT student_name
FROM students
WHERE student_id IN (SELECT student_id FROM enrollments);

Comment: The query in IN always needs to be wrapped in parentheses so SQL knows you want to run a subquery.

Missing Aliases

When you use subqueries in the FROM section, don't forget to give them an alias. Without it, PostgreSQL just gets confused.

Example of a mistake:

SELECT student_name, avg_score
FROM (SELECT student_id, AVG(score) AS avg_score FROM grades GROUP BY student_id)
WHERE avg_score > 80;

Error:

ERROR:  subquery in FROM must have an alias

Fix:

SELECT student_name, avg_score
FROM (SELECT student_id, AVG(score) AS avg_score FROM grades GROUP BY student_id) AS subquery
WHERE avg_score > 80;

Comment: PostgreSQL wants every temp table (the result of a subquery in FROM) to have a name.

Performance Issues

Subqueries, especially if they're not optimized, can turn your database into a sad, slow machine. Performance usually tanks because of extra calculations or missing indexes.

Extra Calculations

Subqueries in the SELECT section can get calculated for every single row in the result, which can eat up a ton of time.

Example:

SELECT student_name,
       (SELECT COUNT(*) FROM enrollments WHERE enrollments.student_id = students.student_id) AS course_count
FROM students;

If the students table has tens of thousands of rows, this subquery will run again for every single row.

Optimization:

WITH course_counts AS (
    SELECT student_id, COUNT(*) AS course_count
    FROM enrollments
    GROUP BY student_id
)
SELECT s.student_name, c.course_count
FROM students s
LEFT JOIN course_counts c ON s.student_id = c.student_id;

CTEs (Common Table Expressions) or joins let you avoid recalculating for every row. We'll get into this approach in a couple more levels :P

Missing Indexes

If you're using heavy subqueries in the WHERE section, make sure the right columns are indexed.

Example:

SELECT student_name
FROM students
WHERE student_id IN (SELECT student_id FROM enrollments WHERE course_id = 10);

If the student_id column in the enrollments table doesn't have an index, the subquery will do a full table scan.

Optimization: Create an index:

CREATE INDEX idx_enrollments_course_id ON enrollments (course_id);

You hear about indexes so much that you're probably already curious what they are. But hang on for a few more levels. Indexes are super important, but they're more about speeding up queries without changing your query code. They won't make a bad query good, but they'll help you speed up queries in production. Especially if you've got tables with millions of rows.

Logic Mistakes

Logic mistakes in subqueries happen just as often as syntax ones. Problems can pop up because you don't really get how NULL, filters, or aggregate functions work.

Wrong Use of NULL

NULL is a trap waiting for every newbie. When you use IN or NOT IN in subqueries, having NULL around can mess up your results.

Example of a mistake:

SELECT student_name
FROM students
WHERE student_id NOT IN (SELECT student_id FROM enrollments);

If the enrollments table has rows with student_id = NULL, the query won't return anything. That's because the NOT IN condition works like NULL IS NOT IN.

Fix:

SELECT student_name
FROM students
WHERE student_id NOT IN (SELECT student_id FROM enrollments WHERE student_id IS NOT NULL);

Always filter out NULL if you're using NOT IN.

Mistakes in Filter Conditions

People often use subqueries for tricky filtering, but mistakes in the conditions can make your results totally different from what you expected.

Example of a mistake:

SELECT student_name
FROM students
WHERE (SELECT AVG(score) FROM grades WHERE grades.student_id = students.student_id) > 80;

If even one student doesn't have any grades, the subquery will return NULL, and that student won't show up in the results.

Fix:

SELECT student_name
FROM students
WHERE COALESCE((SELECT AVG(score) FROM grades WHERE grades.student_id = students.student_id), 0) > 80;

Use COALESCE to swap NULL for default values.

Tips to Avoid Mistakes

To dodge the usual mistakes when working with subqueries, stick to these rules:

Use parentheses and aliases right. If something's not working, check if all your parentheses are closed and if you've set aliases in your subqueries.

Optimize your queries. Try to use subqueries less if you can use JOIN, WITH, or indexes instead.

Watch out for NULL. Always think about NULL showing up in subqueries and use IS NOT NULL, COALESCE, or similar stuff.

Test it out. Test every subquery by itself to make sure it gives you what you want.

Readability. Use indents and aliases so your code is readable. Remember: in a month, you might not even remember what you wrote.