Joins & Aggregations — Series 3

Preview — 3 of 10 questions

What does SELECT * FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id; return?

AThe same rows as INNER JOIN, just in a different column order

BEvery order row, with NULLs for any customer columns that don't match

COnly customers who have placed at least one order

DEvery customer row, matched with their orders where they exist — customers with no orders still appear, with NULLs in the order columns; every order row is guaranteed to match a customer, so it never drops any orders either

Given orders(id, customer_id), customers(id, name), and order_items(order_id, product_id), which query lists each order's customer name alongside its items?

ASELECT c.name, oi.product_id FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id;

BSELECT c.name, oi.product_id FROM orders o, customers c, order_items oi;

CSELECT c.name, oi.product_id FROM customers c JOIN order_items oi ON c.id = oi.order_id;

DIt requires a subquery — JOIN cannot reference more than two tables in one query

Both queries below run against tables that share a column named customer_id. What's the practical difference?

javascript

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
SELECT * FROM orders o JOIN customers c USING (customer_id);

AUSING only works with INNER JOIN, never LEFT/RIGHT/FULL

BThey produce different row sets — USING is stricter than ON

CUSING (customer_id) is shorthand for the equivalent ON condition when both tables share that exact column name — with one difference: the result has a single customer_id column instead of two identically-named ones

DUSING requires the column to be declared as a foreign key first

Answer all 10 questions (7 more), see explanations for every answer, and track your score.