SQL Output Questions: NULL, COUNT and LEFT JOIN Traps with Solved Answers

Quick answer: COUNT(*) counts rows, COUNT(column) counts non-NULL values, and a LEFT JOIN can create a NULL-extended row for an unmatched left-side record. A filter in WHERE can remove that row; the same filter in ON can preserve it. These differences explain many SQL output mistakes.

Use this original ten-question worksheet to practise output prediction, not just query memorisation. It adds focused NULL, aggregation and outer-join traps to the SQL, JOIN and GROUP BY notes. These are general BTech CSE exercises, not previous-year paper questions or a promise about your exam.

One dataset for every question

Courses has three rows. Enrolments has four rows: one missing score and one unassigned course. NULL means a missing or unknown value here, not zero and not the text 'NULL'. student_id is non-NULL because it is the primary key.

Courses

course_id	title
10	DBMS
20	OS
30	Networks

Enrolments

student_id	course_id	score
1	10	80
2	10	NULL
3	20	40
4	NULL	70

Copy the setup SQL into a scratch database

CREATE TABLE Courses(course_id INTEGER PRIMARY KEY, title VARCHAR(40));
CREATE TABLE Enrolments(student_id INTEGER PRIMARY KEY, course_id INTEGER, score INTEGER);
INSERT INTO Courses VALUES (10,'DBMS'),(20,'OS'),(30,'Networks');
INSERT INTO Enrolments VALUES (1,10,80),(2,10,NULL),(3,20,40),(4,NULL,70);

Use a fresh test database, not production tables. These examples use common SQL supported by SQLite and PostgreSQL; output column formatting may differ.

Pocket reference: rows, values and missing data

Expression	Counts or tests	NULL rule
COUNT(*)	Every surviving row	Includes a NULL-extended join row
COUNT(score)	Known score values	Ignores NULL
AVG(score)	Mean of known scores	Does not replace missing values with zero
IS NULL	Whether a value is missing	TRUE or FALSE, unlike = NULL
NOT EXISTS	Absence of a matching subquery row	No match from equality to a NULL value

How to trace a query

1. Build the FROM/JOIN result, including unmatched rows.
2. Apply WHERE: only TRUE survives; FALSE and unknown do not.
3. Form groups and calculate aggregates.
4. Apply HAVING to groups.
5. Read selected columns and apply the explicit ORDER BY.

This is a logical reasoning order, not a claim about a DBMS optimizer's physical execution plan. Without ORDER BY, row order is not guaranteed.

1. COUNT(*) is not COUNT(score)

SELECT COUNT(*) AS rows_total, COUNT(score) AS known_scores,
       COUNT(DISTINCT course_id) AS distinct_courses
FROM Enrolments;

Checked output:

rows_total	known_scores	distinct_courses
4	3	2

All four enrolment rows count toward COUNT(*). The NULL score does not count toward COUNT(score). DISTINCT course IDs are 10 and 20; NULL is not counted, so COUNT(DISTINCT course_id) is 2.

2. An unmatched course still creates a joined row

SELECT c.course_id, c.title, COUNT(*) AS joined_rows,
       COUNT(e.student_id) AS students, COUNT(e.score) AS known_scores
FROM Courses AS c
LEFT JOIN Enrolments AS e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title
ORDER BY c.course_id;

Checked output:

course_id	title	joined_rows	students	known_scores
10	DBMS	2	2	1
20	OS	1	1	1
30	Networks	1	0	0

DBMS matches two rows, OS matches one, and Networks matches none. The LEFT JOIN supplies one NULL-extended row for Networks: COUNT(*) counts it, but COUNT(e.student_id) and COUNT(e.score) do not. Student 4 has no matching course, so is absent from a join that preserves Courses, not Enrolments.

See the NULL-extended join row

Courses LEFT JOIN Enrolments

10 DBMS -----> 1: score 80
        -----> 2: score NULL
20 OS -------> 3: score 40
30 Networks -> no match
               student NULL
               score NULL

Student 4: course NULL, score 70
Not matched to any course.

WHERE score >= 50:
  keep course 10 / student 1

ON ... AND score >= 50:
  10 -> student 1
  20 -> NULL-extended row
  30 -> NULL-extended row

The placeholder for course 30 is a result row, not an enrolment inserted into the table. That is why COUNT(*) is 1 while COUNT(e.student_id) is 0.

3. A right-table WHERE condition removes unmatched rows

SELECT c.course_id, e.student_id, e.score
FROM Courses AS c
LEFT JOIN Enrolments AS e ON e.course_id = c.course_id
WHERE e.score >= 50
ORDER BY c.course_id, e.student_id;

Checked output:

course_id	student_id	score
10	1	80

The join happens before the WHERE filter. Score 80 passes, NULL does not evaluate to TRUE, and 40 fails. Networks has a NULL-extended row which also fails. A LEFT JOIN does not guarantee all left rows survive later filters.

4. The same condition in ON preserves every course

SELECT c.course_id, e.student_id, e.score
FROM Courses AS c
LEFT JOIN Enrolments AS e
  ON e.course_id = c.course_id AND e.score >= 50
ORDER BY c.course_id, e.student_id;

Checked output:

course_id	student_id	score
10	1	80
20	NULL	NULL
30	NULL	NULL

Now score >= 50 decides which enrolments qualify as matches. DBMS matches student 1. OS and Networks have no qualifying match, so each gets one NULL-extended row. This is not the same output as Question 3.

5. WHERE filters rows, HAVING filters groups

SELECT course_id, COUNT(*) AS students, AVG(score) AS avg_score
FROM Enrolments
WHERE course_id IS NOT NULL
GROUP BY course_id
HAVING COUNT(*) >= 2
ORDER BY course_id;

Checked output:

course_id	students	avg_score
10	2	80.0

WHERE removes student 4 before grouping. DBMS has two rows, including the row with NULL score; it passes HAVING. OS has only one row and fails. AVG ignores the NULL score, so DBMS average is 80, not 40.

6. NULL in NOT IN is an exam trap

SELECT course_id FROM Courses
WHERE course_id NOT IN (SELECT course_id FROM Enrolments)
ORDER BY course_id;

Checked output:

0 rows (empty result).

The subquery returns 10, 10, 20 and NULL. For 30, the comparisons against known IDs do not find a match, but the comparison with NULL is unknown. NOT IN therefore is not TRUE for 30. IDs 10 and 20 have matches. No course row passes. Do not read the empty result as proof that Networks has an enrolment.

7. NOT EXISTS safely expresses no matching enrolment

SELECT c.course_id, c.title FROM Courses AS c
WHERE NOT EXISTS (
  SELECT 1 FROM Enrolments AS e WHERE e.course_id = c.course_id
)
ORDER BY c.course_id;

Checked output:

course_id	title
30	Networks

Networks has no matching enrolment, so NOT EXISTS is TRUE for course 30. The unassigned NULL course_id does not create a match. An alternative for this dataset is to exclude NULL inside the NOT IN subquery before testing it.

Three self-test queries

Predict every row before opening each answer. All questions use the unchanged dataset above.

A. Predict the output

SELECT course_id, COUNT(score) AS scored
FROM Enrolments
GROUP BY course_id
ORDER BY CASE WHEN course_id IS NULL THEN 1 ELSE 0 END, course_id;

Show checked output and explanation

course_id	scored
10	1
20	1
NULL	1

The NULL course IDs form one group. COUNT(score) counts non-NULL scores, not rows.

B. Which course has zero students?

SELECT c.course_id, COUNT(e.student_id) AS students
FROM Courses AS c
LEFT JOIN Enrolments AS e ON e.course_id = c.course_id
GROUP BY c.course_id
HAVING COUNT(e.student_id) = 0
ORDER BY c.course_id;

Show checked output and explanation

course_id	students
30	0

Networks remains in the left join and has zero non-NULL student IDs.

C. Fix NOT IN by filtering the subquery

SELECT course_id FROM Courses
WHERE course_id NOT IN (
  SELECT course_id FROM Enrolments WHERE course_id IS NOT NULL
)
ORDER BY course_id;

Show checked output and explanation

course_id
30

The subquery now contains only 10,10,20. Course 30 passes.

Run all ten output questions

Run this standalone Python 3 script. It uses an in-memory SQLite database, recreates the exact dataset and runs Questions 1-7 followed by self-tests A-C. Python displays SQL NULL as None and an empty result as []. These are output-display differences, not changed rows.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE Courses(course_id INTEGER PRIMARY KEY, title VARCHAR(40));
CREATE TABLE Enrolments(student_id INTEGER PRIMARY KEY, course_id INTEGER, score INTEGER);
INSERT INTO Courses VALUES (10,'DBMS'),(20,'OS'),(30,'Networks');
INSERT INTO Enrolments VALUES (1,10,80),(2,10,NULL),(3,20,40),(4,NULL,70);
""")
queries = [
    """SELECT COUNT(*) AS rows_total, COUNT(score) AS known_scores,
       COUNT(DISTINCT course_id) AS distinct_courses
FROM Enrolments;""",
    """SELECT c.course_id, c.title, COUNT(*) AS joined_rows,
       COUNT(e.student_id) AS students, COUNT(e.score) AS known_scores
FROM Courses AS c
LEFT JOIN Enrolments AS e ON e.course_id = c.course_id
GROUP BY c.course_id, c.title
ORDER BY c.course_id;""",
    """SELECT c.course_id, e.student_id, e.score
FROM Courses AS c
LEFT JOIN Enrolments AS e ON e.course_id = c.course_id
WHERE e.score >= 50
ORDER BY c.course_id, e.student_id;""",
    """SELECT c.course_id, e.student_id, e.score
FROM Courses AS c
LEFT JOIN Enrolments AS e
  ON e.course_id = c.course_id AND e.score >= 50
ORDER BY c.course_id, e.student_id;""",
    """SELECT course_id, COUNT(*) AS students, AVG(score) AS avg_score
FROM Enrolments
WHERE course_id IS NOT NULL
GROUP BY course_id
HAVING COUNT(*) >= 2
ORDER BY course_id;""",
    """SELECT course_id FROM Courses
WHERE course_id NOT IN (SELECT course_id FROM Enrolments)
ORDER BY course_id;""",
    """SELECT c.course_id, c.title FROM Courses AS c
WHERE NOT EXISTS (
  SELECT 1 FROM Enrolments AS e WHERE e.course_id = c.course_id
)
ORDER BY c.course_id;""",
    """SELECT course_id, COUNT(score) AS scored
FROM Enrolments
GROUP BY course_id
ORDER BY CASE WHEN course_id IS NULL THEN 1 ELSE 0 END, course_id;""",
    """SELECT c.course_id, COUNT(e.student_id) AS students
FROM Courses AS c
LEFT JOIN Enrolments AS e ON e.course_id = c.course_id
GROUP BY c.course_id
HAVING COUNT(e.student_id) = 0
ORDER BY c.course_id;""",
    """SELECT course_id FROM Courses
WHERE course_id NOT IN (
  SELECT course_id FROM Enrolments WHERE course_id IS NOT NULL
)
ORDER BY course_id;"""
]
for i, query in enumerate(queries, 1):
    print(i, db.execute(query).fetchall())

Executed output:

1 [(4, 3, 2)]
2 [(10, 'DBMS', 2, 2, 1), (20, 'OS', 1, 1, 1), (30, 'Networks', 1, 0, 0)]
3 [(10, 1, 80)]
4 [(10, 1, 80), (20, None, None), (30, None, None)]
5 [(10, 2, 80.0)]
6 []
7 [(30, 'Networks')]
8 [(10, 1), (20, 1), (None, 1)]
9 [(30, 0)]
10 [(30,)]

Exam and interview quick answers

1. Why does Networks have one joined row but zero students? LEFT JOIN adds a NULL-extended row; COUNT(*) sees the row, COUNT(e.student_id) ignores its NULL.
2. Does AVG include a missing score as zero? No. Only known scores contribute to the mean.
3. Why is student 4 absent from the Courses LEFT JOIN? The preserved side is Courses. A NULL course_id does not match a course by equality.
4. Does a LEFT JOIN preserve rows after any WHERE filter? No. A filter can remove the NULL-extended rows.
5. Why can moving a filter from WHERE to ON change the result? ON controls matches; WHERE filters the joined result.
6. How should missing values be tested? Use IS NULL or IS NOT NULL, not = NULL.
7. What is the danger in NOT IN? A NULL in the subquery can make an otherwise unmatched comparison unknown rather than TRUE.
8. Is query output automatically ordered? No. Write ORDER BY when row order matters.

Common mistakes and a final checklist

• Do not turn NULL into 0 while calculating AVG.
• Do not use COUNT(*) to count matched children after an outer join. Count a non-NULL key from the child table.
• Do not assume a right-side condition in WHERE is equivalent to one in ON.
• Use IS NULL or IS NOT NULL, not = NULL, to test missing values.
• Check whether a NOT IN subquery can contain NULL.
• Keep duplicates unless DISTINCT or grouping removes them.
• Never invent an output order when ORDER BY is absent.

Before submitting an exam answer, write the join rows, mark every NULL, count rows and values separately, and state the exact output columns. That working makes mistakes easier to catch.

Keep practising

• Read the broad SQL chapter and JOIN introduction
• Run the complete DBMS SQL lab practicals
• Connect SQL filters and joins to relational algebra
• Return to the worked-practice hub

Sources and verification

Semantics checked against official PostgreSQL documentation: table expressions, outer joins, WHERE and HAVING, aggregate functions and NULL handling, IN, NOT IN and NULL, and SELECT and output ordering. All datasets, questions and explanations on this page are original. Every displayed query was executed against the shown dataset using Python's SQLite database engine; the common SQL semantics were cross-checked with the PostgreSQL references. This is not a claim that every SQL dialect uses identical syntax, data types or display formatting.

Related DBMS practice: candidate keys and attribute closure and BCNF vs 3NF.