Tip of The Day
Outer joins and placement
SELECT C.customer_name, SUM(COALESCE(O.order_amt, 0)) AS total_2009
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
ON C.customer_nbr = O.customer_nbr
WHERE O.order_date >= '20090101'
GROUP BY C.customer_name;
SELECT C.customer_name, O.order_amt, D.qty
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
ON C.customer_nbr = O.customer_nbr
INNER JOIN OrderDetails ASD
ON D.order_nbr = O.order_nbr
AND D.sku =101;
Outer joins and placement of predicates (solutions)
-- Correct predicate in join conditions
SELECT C.customer_name, SUM(COALESCE(O.order_amt, 0)) AS total_2009
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
ON C.customer_nbr = O.customer_nbr
AND O.order_date >= '20090101'
GROUP BY C.customer_name;
-- Correct with OUTER join
SELECT C.customer_name, O.order_amt, D.qty
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
ON C.customer_nbr = O.customer_nbr
LEFT OUTER JOIN OrderDetails ASD
ON D.order_nbr = O.order_nbr
AND D.sku =101;