Subqueries & EXISTS

Scalar subqueries

SELECT name_en, cgpa
FROM students
WHERE cgpa > (SELECT avg(cgpa) FROM students);

SELECT name_en,
       cgpa - (SELECT avg(cgpa) FROM students) AS above_average
FROM students;

A scalar subquery must return one row and one column, or you get a runtime error.

Subqueries in the column list

SELECT
  d.name_en,
  (SELECT count(*) FROM students s WHERE s.department = d.code) AS total
FROM departments d;

This runs once per outer row. A LEFT JOIN … GROUP BY or a LATERAL subquery is usually faster on large tables.

IN and NOT IN

SELECT * FROM students
WHERE department IN (SELECT code FROM departments WHERE active);

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

The NOT IN trap: if the subquery returns even one NULL, the whole condition becomes unknown and you get zero rows. Use NOT EXISTS instead — it handles NULL correctly and usually plans better.

EXISTS and NOT EXISTS

SELECT s.name_en
FROM students s
WHERE EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

SELECT s.name_en
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

EXISTS stops at the first matching row. The SELECT 1 is a convention — the select list is never evaluated.

ANY, SOME and ALL

-- higher than at least one EEE student
SELECT * FROM students
WHERE cgpa > ANY (SELECT cgpa FROM students WHERE department = 'EEE');

-- higher than every EEE student
SELECT * FROM students
WHERE cgpa > ALL (SELECT cgpa FROM students WHERE department = 'EEE');

-- ANY also works with arrays
SELECT * FROM students WHERE id = ANY(ARRAY[1,2,3]);

Correlated subqueries

SELECT s.name_en, s.department, s.cgpa
FROM students s
WHERE s.cgpa > (
  SELECT avg(s2.cgpa) FROM students s2 WHERE s2.department = s.department
);

The inner query references the outer row, so it is re-evaluated for each candidate row. A window function usually expresses the same idea more efficiently:

SELECT * FROM (
  SELECT *, avg(cgpa) OVER (PARTITION BY department) AS dept_avg
  FROM students
) t WHERE cgpa > dept_avg;

Derived tables

SELECT department, avg_cgpa
FROM (
  SELECT department, avg(cgpa) AS avg_cgpa
  FROM students GROUP BY department
) AS dept_stats
WHERE avg_cgpa > 3.0;

A derived table must have an alias. A CTE is usually clearer for the same job.

Row constructors

SELECT * FROM enrollments
WHERE (student_id, course_id) IN (SELECT student_id, course_id FROM transfers);

SELECT * FROM students
WHERE (department, cgpa) = (SELECT department, max(cgpa) FROM students GROUP BY department LIMIT 1);

Row comparison is standard SQL and fully supported in Postgres, which makes multi-column matching much less awkward than chaining AND conditions.