SQL II: queries, joins and aggregation

Most work with a database is asking questions of it. This lesson covers the core of SQL querying: the SELECT-FROM-WHERE block, sorting and limiting results, conditions and the three-valued logic that NULL brings, the different kinds of join, and aggregation with GROUP BY and HAVING. All examples run against the college database defined in lesson 3. By the end you should be able to translate ordinary questions, such as 'which instructors teach more than one section, and what is their average class grade?', into correct SQL, and to predict what a query returns before you run it.

The basic query block

A query has the shape SELECT columns FROM tables WHERE condition. FROM names the tables; WHERE keeps the rows for which the condition is true; SELECT chooses the output columns, which may be expressions, and can rename them with AS. In relational algebra terms, FROM is a Cartesian product, WHERE is a selection and SELECT is a projection, except that SQL keeps duplicate rows unless you write SELECT DISTINCT. ORDER BY sorts the result, ascending by default or DESC for descending, and LIMIT keeps only the first rows. Without ORDER BY, the order of rows is undefined and may change between runs or versions, even if it looks stable.

SELECT name, credits, credits / 3 AS approx_courses
FROM student
WHERE credits >= 45
ORDER BY credits DESC;

SELECT DISTINCT dept_id FROM course ORDER BY dept_id;

SELECT course_id, title FROM course
WHERE title LIKE '%Algorithms%' OR course_id IN ('MATH-125', 'STAT-151');

name   credits  approx_courses
-----  -------  --------------
Grace  90       30
Chloe  87       29
Elena  66       22
Ava    54       18
Farid  45       15

dept_id
-------
BIO
CS
MATH
PHYS

course_id  title
---------  --------------------------
CMPUT-204  Algorithms I
CMPUT-304  Algorithms II
MATH-125   Linear Algebra I
STAT-151   Introduction to Statistics

Integer division explains the approx_courses column: in SQLite, PostgreSQL and SQL Server, dividing two integers truncates, so 54 / 3 is 18 and 45 / 3 is 15, while MySQL returns a decimal. Write 54 / 3.0 or CAST(credits AS REAL) to get a fractional result portably. Conditions can use comparison operators, AND, OR and NOT, BETWEEN low AND high (inclusive), IN (list), and LIKE with the wildcards % (any sequence of characters) and _ (exactly one character). Whether LIKE is case-sensitive varies by system: SQLite's is case-insensitive for ASCII letters, PostgreSQL's is case-sensitive and offers ILIKE.

How SQL evaluates a query

The clauses of a query are written in one order and evaluated, logically, in another. Understanding the logical order explains many puzzling errors, such as why a column alias defined in SELECT cannot be used in WHERE in standard SQL.

The DBMS may physically compute things in a different order, using indexes and pushing work around as lesson 9 shows, but the answer must be as if it followed these steps. Because WHERE runs before SELECT, a condition on an alias defined in SELECT is not allowed in standard SQL (SQLite and MySQL accept it as an extension); repeat the expression or use a subquery. Because WHERE runs before GROUP BY, conditions on aggregates such as COUNT(*) must go in HAVING, not WHERE.

NULL and three-valued logic

A comparison involving NULL is neither true nor false; its result is unknown. So credits > 50 is unknown when credits is NULL, and so is NULL = NULL. SQL therefore uses three-valued logic with true, false and unknown. WHERE keeps only rows whose condition is true, so rows where it is unknown are dropped, just like false ones. To test for NULL, use IS NULL and IS NOT NULL, never = NULL, which is always unknown.

-- Which students have no department?
SELECT name FROM student WHERE dept_id = NULL;      -- always unknown: returns nothing
SELECT name FROM student WHERE dept_id IS NULL;     -- correct

-- NOT IN with a NULL in the list returns nothing at all
SELECT name FROM instructor WHERE dept_id NOT IN ('CS', NULL);

-- COALESCE substitutes a value for NULL in output
SELECT name, COALESCE(dept_id, '(none)') AS dept FROM student WHERE student_id IN (101, 108);

name
----
(no rows)

name
----
Hiro

name
----
(no rows)

name  dept
----  ------
Ava   CS
Hiro  (none)

The NOT IN result surprises almost everyone. The condition dept_id NOT IN ('CS', NULL) means dept_id <> 'CS' AND dept_id <> NULL. The second part is unknown for every row, so the whole condition is at best unknown, and no row is ever kept. The same trap appears when NOT IN is used with a subquery that can return a NULL; lesson 5 shows the safer NOT EXISTS. Aggregate functions also treat NULL specially: COUNT(column), SUM, AVG, MIN and MAX ignore NULLs, while COUNT(*) counts rows. An average of grades therefore averages only the grades that exist, which is usually, but not always, what you want.

Joins

Joins combine rows from several tables. The modern syntax writes the join in FROM with JOIN ... ON condition. An inner join keeps pairs of rows that satisfy the condition. The older style lists tables separated by commas and puts the join condition in WHERE; it means the same for inner joins but makes it easy to forget a condition, which silently produces a Cartesian product. USING (column) is shorthand for equality on same-named columns and outputs the column once; NATURAL JOIN joins on all same-named columns, which is fragile, because adding a column to one table can silently change the join.

-- Inner join: each student with the name of their department
SELECT s.name AS student, d.name AS department
FROM student AS s
JOIN department AS d ON s.dept_id = d.dept_id
ORDER BY d.name, s.name;

-- Left outer join: every department, with its number of students (0 if none)
SELECT d.name, COUNT(s.student_id) AS students
FROM department AS d
LEFT JOIN student AS s ON s.dept_id = d.dept_id
GROUP BY d.dept_id
ORDER BY students DESC, d.name;

student  department
-------  -----------------
Elena    Biology
Jonah    Biology
Ava      Computing Science
Ben      Computing Science
Dev      Computing Science
Grace    Computing Science
Chloe    Mathematics
Isla     Mathematics
Farid    Physics

name               students
-----------------  --------
Computing Science  4
Biology            2
Mathematics        2
Physics            1
History            0

The table aliases s and d shorten the query and are required when the same table appears twice. A left outer join keeps every row of the left table; where no right row matches, the right columns are NULL. A right outer join does the reverse, and a full outer join keeps unmatched rows from both sides. SQLite supports RIGHT and FULL joins since version 3.39; MySQL and MariaDB still do not support FULL OUTER JOIN, which can be written as a union of a left and a right join. A useful habit is to count with COUNT(column from the right table) after a left join, so that unmatched rows count as zero rather than one.

-- Self-join: each instructor with their mentor's name
SELECT i.name AS instructor, m.name AS mentor
FROM instructor AS i
LEFT JOIN instructor AS m ON i.mentor_id = m.instr_id
ORDER BY i.instr_id;

-- Three-table join: who teaches which course, and when
SELECT c.course_id, sec.term, sec.year, i.name AS instructor
FROM section AS sec
JOIN course AS c ON c.course_id = sec.course_id
LEFT JOIN instructor AS i ON i.instr_id = sec.instr_id
WHERE c.dept_id = 'CS'
ORDER BY sec.year, sec.term, c.course_id;

instructor  mentor
----------  --------
Okafor      NULL
Lindqvist   Okafor
Tremblay    Okafor
Nakamura    NULL
Ferreira    Nakamura
Chen        NULL
Haddad      NULL
Morrison    NULL

course_id  term    year  instructor
---------  ------  ----  ----------
CMPUT-174  Fall    2025  Tremblay
CMPUT-204  Fall    2025  Okafor
CMPUT-272  Fall    2025  Nakamura
CMPUT-174  Winter  2026  NULL
CMPUT-201  Winter  2026  Lindqvist
CMPUT-291  Winter  2026  Lindqvist

Aggregation and grouping

Aggregate functions compute one value from many rows: COUNT, SUM, AVG, MIN and MAX, plus system-specific ones such as SQLite's group_concat or PostgreSQL's string_agg. Without GROUP BY, an aggregate summarizes the whole result. With GROUP BY, rows are partitioned into groups that share the grouping values, and each group produces one output row. HAVING then filters groups, as WHERE filters rows.

-- Per section: enrolled students, graded students and average grade
SELECT e.sec_id, sec.course_id,
       COUNT(*)             AS enrolled,
       COUNT(e.grade)       AS graded,
       ROUND(AVG(e.grade), 1) AS avg_grade
FROM enrollment AS e
JOIN section AS sec ON sec.sec_id = e.sec_id
GROUP BY e.sec_id, sec.course_id
HAVING COUNT(e.grade) >= 2
ORDER BY avg_grade DESC;

-- Instructors teaching more than one section
SELECT i.name, COUNT(*) AS sections
FROM instructor AS i JOIN section AS sec ON sec.instr_id = i.instr_id
GROUP BY i.instr_id
HAVING COUNT(*) > 1;

sec_id  course_id  enrolled  graded  avg_grade
------  ---------  --------  ------  ---------
3       CMPUT-204  2         2       87.5
6       MATH-125   4         4       81.5
4       CMPUT-272  5         5       79.6
1       CMPUT-174  4         4       77.8
8       BIOL-107   2         2       71.5

name       sections
---------  --------
Lindqvist  2
Ferreira   2

COUNT(*) counts rows and COUNT(e.grade) counts non-NULL grades, which is why enrolled and graded differ for sections with students still in progress. The rule for grouped queries is that every column in SELECT must be either a grouping column or inside an aggregate. A query such as SELECT dept_id, name, COUNT(*) FROM student GROUP BY dept_id is ill-formed, because a department has many student names and SQL does not know which one to show. The standard rejects it, and so do PostgreSQL and MySQL in their default modes, but SQLite accepts it and returns a name from an arbitrary row of the group, a convenience that hides bugs.

Set operations

UNION, INTERSECT and EXCEPT combine the results of two queries with compatible columns. UNION and the others remove duplicates, as in relational algebra; UNION ALL keeps them and is faster because it does not have to check. They are useful when the two parts come from different tables or need different conditions.

-- People in Computing Science, whether students or instructors
SELECT name, 'student' AS role FROM student WHERE dept_id = 'CS'
UNION
SELECT name, 'instructor' FROM instructor WHERE dept_id = 'CS'
ORDER BY role, name;

-- Courses that have a section but are nobody's prerequisite
SELECT course_id FROM section
EXCEPT
SELECT prereq_id FROM prereq;

name       role
---------  ----------
Lindqvist  instructor
Okafor     instructor
Tremblay   instructor
Ava        student
Ben        student
Dev        student
Grace      student

course_id
---------
BIOL-107
CMPUT-291
MATH-125
PHYS-130
STAT-151

Note that ORDER BY applies to the whole combined result and is written once at the end. MySQL added INTERSECT and EXCEPT only in version 8.0.31; older code emulates them with joins and subqueries. Set operations compare whole rows, and for this purpose two NULLs are treated as equal, an exception to the usual rule that NULL equals nothing.