Scalar and IN subqueries

Part of the free LogicWiz SQL & Databases course, Module 6: Subqueries and set operations. The queries and practice run in the app on real Postgres.

The placement cell wants every student with a CGPA above the college average. Meher writes WHERE cgpa > AVG(cgpa) and Postgres refuses. The average is one number; how do you get it into the WHERE?

You'll learn to

  • Use a scalar subquery as a single value
  • Filter with IN (subquery)
  • Know what happens when a subquery returns 0 or many rows
  • Solve "second highest" with a subquery

Learn

A query inside a query

A subquery is a SELECT in brackets, used inside another query. Think of it as solving the problem in two steps:

  1. Find the average CGPA (one number).
  2. Find students above that number.

Instead of running step 1, copying the number and pasting it into step 2, you put step 1 inside step 2. Postgres works out the inner result and uses it.

Above the college average

SELECT name, cgpa
FROM students
WHERE cgpa > (SELECT AVG(cgpa) FROM students)
ORDER BY cgpa DESC, name;

The inner query returns one row and one column, about 7.84. A subquery like that is called a scalar subquery: you can use it anywhere a single value fits. Aditya (no CGPA yet) is not compared at all: NULL > anything is unknown.

Why not just WHERE cgpa > AVG(cgpa)?

SELECT name
FROM students
WHERE cgpa > AVG(cgpa);

WHERE looks at one row at a time, before any grouping happens, so an aggregate has nothing to work on yet. Postgres says aggregates are not allowed in WHERE. The subquery computes the average separately, first.

One value, zero, or many

A scalar subquery must give at most one row and one column.

  • One row: its value is used.
  • Zero rows: the value is NULL. cgpa > NULL is never true, so the outer query quietly returns nothing.
  • Two or more rows: an error, more than one row returned by a subquery used as an expression.

When the inner query can return a list, use IN instead of =.

IN (subquery): match against a list

SELECT name, year
FROM students
WHERE student_id IN (SELECT student_id
                     FROM enrollments
                     WHERE course_id = 'CS301')
ORDER BY name;

The inner query returns a list of student ids (who took Database Systems). IN keeps a student whose id is anywhere in that list.

A scalar subquery in SELECT

SELECT name, cgpa,
       ROUND(cgpa - (SELECT AVG(cgpa) FROM students), 2) AS vs_average
FROM students
WHERE dept_id = 1
ORDER BY vs_average DESC NULLS LAST, name;

A scalar subquery can sit in the SELECT list too. Here every CS student sees how far above or below the college average they are.

WHERE dept_id = (SELECT dept_id FROM departments WHERE building = 'A Block') works today only if A Block has exactly one department. It has two (Computer Science and Mathematics), so it errors. If the subquery can return several rows, write IN.

"Find the second highest salary" is the most asked SQL interview question of all. One clean answer: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);. It handles ties at the top and returns NULL (not an error) when there is no second value. Module 11 shows the ranking-function way.

In one line

A scalar subquery (SELECT AVG(…) …) is one value (NULL if empty, error if many); use IN (SELECT …) for a list.