The Library Query That Multiplies Authors by Books
The library at a school in Amritsar keeps its data in two tables. BOOKS has 20 rows and the columns BookID, Title, AuthorID and Price. AUTHORS has 4 rows and the columns AuthorID and Name. One of the 20 books is a compilation whose AuthorID is NULL, and two of the four authors have written no book currently held in the library.
The librarian wants a list of every author with the number of that author's books priced above ₹150, and writes:
SELECT Name, COUNT(*) FROM AUTHORS, BOOKS WHERE Price > 150 GROUP BY Name;
The query runs without error, but the four counts added together give a number far larger than 20, and an author who has never written a book in the library still appears with a count of 9. Meanwhile the anonymous compilation is never counted for anyone, which the librarian accepts as reasonable.
When the librarian tries LEFT JOIN ... ON AUTHORS.AuthorID = BOOKS.AuthorID with the same WHERE Price > 150, the two bookless authors vanish again. Moving the price condition into the ON clause brings them back, but each now shows a count of 1 instead of 0.
1. Explain why the first query produces counts far larger than 20 and why a bookless author still gets a large count. Rewrite the query as an inner join that gives correct counts for authors who do have books.
Read the worked answer
Writing two tables in the FROM clause separated by a comma is a cross join followed by a filter. There is no condition tying AUTHORS.AuthorID to BOOKS.AuthorID, so the intermediate result contains every author paired with every qualifying book: 4 authors × every book priced above ₹150. If 20 books qualify, the intermediate relation has 80 rows and each author's group has 20 rows. That is why the counts are inflated and why their total is a multiple well above 20 - and why the bookless author still scores 9: that author is paired with the same 9 qualifying books as everyone else. The GROUP BY Name groups the paired rows; it does not create the pairing.
Inflation is not random - it is the number of qualifying rows in BOOKS, repeated once per author. That is the diagnostic signature of a missing join condition.
Correct inner-join version:
SELECT A.Name, COUNT(B.BookID) AS Books_Above_150
FROM AUTHORS A
JOIN BOOKS B ON A.AuthorID = B.AuthorID
WHERE B.Price > 150
GROUP BY A.AuthorID, A.Name;
Grouping by A.AuthorID as well as the name is good practice: two authors could share a name, and grouping on the key prevents them being merged. Note that the anonymous book, whose AuthorID is NULL, matches no author under = and drops out - consistent with what the librarian expects.
2. The librarian wants all four authors listed, with 0 for the two who have no qualifying book. Explain why the LEFT JOIN with `WHERE Price > 150` still drops them, why moving the condition to ON gives 1 instead of 0, and give the final query.
Read the worked answer
Why WHERE drops them. A LEFT JOIN keeps unmatched left-side rows and fills the right side with NULLs. But WHERE is applied after the join. For a bookless author the joined row is (author, NULL, NULL); the predicate B.Price > 150 evaluates to UNKNOWN on NULL, and UNKNOWN is not TRUE, so the row is filtered out. Putting the condition in WHERE therefore silently converts the outer join back into an inner join - a very common and very quiet mistake.
Why COUNT(*) gives 1. With the condition in the ON clause, the join is performed first and the LEFT JOIN keeps the unmatched author with one NULL-extended row. COUNT(*) counts rows, and that row exists, so the answer is 1. COUNT(B.BookID) counts only non-NULL values of that column, and the padded BookID is NULL, so the answer is 0 - which is the number the librarian actually wants.
Final query:
SELECT A.Name, COUNT(B.BookID) AS Books_Above_150
FROM AUTHORS A
LEFT JOIN BOOKS B
ON A.AuthorID = B.AuthorID
AND B.Price > 150
GROUP BY A.AuthorID, A.Name;
Rule of thumb: filters on the preserved table belong in WHERE; filters on the optional table belong in ON. And when a count of related records is wanted through an outer join, count the foreign-key column, not *.
3. The Price column allows NULL, meaning "not yet priced". The librarian wants two different numbers: the count of books priced above ₹150, and the total count of books held. Explain how NULL behaves in each case and give the two expressions, warning her about one trap.
Read the worked answer
NULL is not zero and not an empty string - it means unknown. Any ordinary comparison with NULL (Price > 150, Price = NULL, Price <> 150) evaluates to UNKNOWN, so the row is never returned by a WHERE filter. Unpriced books are therefore automatically excluded from "priced above ₹150", which is the desired behaviour and needs no extra code.
Expressions:
- Books priced above ₹150:
SELECT COUNT(*) FROM BOOKS WHERE Price > 150;HereCOUNT(*)is safe because WHERE has already removed the unpriced rows. - Total books held:
SELECT COUNT(*) FROM BOOKS;This counts rows, including rows whose Price is NULL.
The trap is assuming COUNT(Price) or AVG(Price) behave the same. COUNT(column) skips NULLs, so COUNT(Price) gives the number of priced books, not the number of books - the two answers differ by the number of unpriced books, which is exactly the figure the librarian must not lose. AVG(Price) also ignores NULL rows, so it reports the average of priced books only; that may be what she wants, but it must be stated, because dividing a sum by the full book count would give a different and wrong figure. Also, Price <> 150 silently drops unpriced books, and WHERE Price IS NULL (not = NULL) is the only way to find them.