Skip to Content
SQL Intermediate: Queries That Survive Real Data
Intermediate
0 likes
Courses
SQL Intermediate: Queries That Survive Real Data

Check Yourself: Joins Beyond the Basics

Five scenario questions on fan-out, pre-aggregation, EXISTS and NOT IN with NULL. If you miss more than one, reread the lessons in this section.

5 min SQLDatabasesPerformance Optimization Intermediate
Prev Next
Fullscreen Share
Course content

Sign in to save your progress.

Course Home
1. A report joins customers, orders and order_items, then adds up a shipping fee that is stored on each order. The shipping total is too high, while the item totals are right. What happened?
SUM adds every column twice when two tables are joined. PostgreSQL cannot sum a column from the orders table after a join. Each order's fee is repeated once per item row, so it was added once for every item. The LEFT JOIN created extra rows with NULL fees.
2. You want the shipping total per customer to be right. Which fix is correct?
Use SUM(DISTINCT shipping). Add one more join to the products table. Add up the items per order in a subquery first, so the join has one row per order. Change the INNER JOIN to a RIGHT JOIN.
3. You need every customer with the number of orders they placed, including customers who have none, shown as 0. Which query is right?
INNER JOIN orders to customers and use count of all rows. LEFT JOIN, then keep only rows where the order id is not NULL. LEFT JOIN, but count all rows with count star. LEFT JOIN orders to customers and use count of the order id column.
4. A query lists customers whose city is NOT IN a subquery that returns cities. It returns no rows, although you know some customers qualify. One customer in the subquery has no city. What is the cause?
A NULL in the list makes NOT IN unknown for every row, so nothing is kept. The subquery must be written as a join. NOT IN only works with numbers. PostgreSQL ignores NULL values inside a subquery.
5. Ben bought a backpack in two separate orders. You want each customer who bought from the bags category listed once. Which form says that directly?
UNION ALL of the bag orders. WHERE customer_id IN (subquery that finds bag buyers), or an EXISTS test. CROSS JOIN customers and products. A plain join of customers, orders, items and products.

About

Five scenario questions on fan-out, pre-aggregation, EXISTS and NOT IN with NULL. If you miss more than one, reread the lessons in this section.

Duration
5 min
Last update
09/27/2026
Was this helpful?
​ 0 ​ 0

Comments (0)

Sign in to comment

No comments yet.