Avoid Missing Rows - Don't Make This SQL Mistake | Essential SQL

When working with queries, specifically joining one or more tables together, it is easy to "drop" rows. The cause? The inner join.

Depending on the situation, consider using outer joins to avoid unintentionally dropping rows.


One Last Thing...
This video is part of my Intermediate Learning series. If you're interested in learning SQL subscribe to @Essential SQL and then check out our Intermediate Learner Playlist. Of course, I also encourage you to visit https://www.essentialsql.com to learn even more!

Important links:
Sample PizzaDB: https://github.com/kwenzel1/EssentialSQLSamples/tree/master/PizzaDB
Corresponding Article:

Source Code:

--First Query
select o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, cp.PercentDiscount
from CustomerOrder o
inner join Customer c on o.CustomerID = c.CustomerID
inner join Coupon cp on o.CouponID = cp.CouponID


--Sanity Check
select count(1)
from CustomerOrder


--Query Fix
select o.CustomerOrderID, o.OrderDate, c.CustomerID, c.LastName, cp.PercentDiscount
from CustomerOrder o
inner join Customer c on o.CustomerID = c.CustomerID
left join Coupon cp on o.CouponID = cp.CouponID




--Summary
select c.LastName, Avg(cp.PercentDiscount) AvgDiscountTaken
from CustomerOrder o
inner join Customer c on o.CustomerID = c.CustomerID
left join Coupon cp on o.CouponID = cp.CouponID
Group By c.LastName


--Summary Accounting for Null
select c.LastName, Avg(isnull(cp.PercentDiscount,0)) AvgDiscountTaken
from CustomerOrder o
inner join Customer c on o.CustomerID = c.CustomerID
left join Coupon cp on o.CouponID = cp.CouponID
Group By c.LastName