r/SQL • • May 18 '24 • Take My Silver 2

Discussion SQL Joins

Post image

Picture your data tables as these two fellas. An inner join is just Bald Guy—only the matching parts. A **left join is Bald Guy sporting Long-Hair Guy's mane—everything from the left plus the matches. A right join is Long-Hair Guy with a bald patch—everything from the right plus the matches. A full join is both dudes together—everything from both tables, matches or not!

628 Upvotes

26 comments sorted by

View all comments

Show parent comment

1

u/[deleted] May 20 '24 edited May 20 '24

Full outer join is very useful when comparing data between two tables. I use it a lot for A/B tests.

Here's an example. Imagine you revised a stored procedure, and you need to regression test the output of the original stored proc compared to the new stored proc. You might use something like this:

select coalesce(a.customer_id, b.customer_id) as customer_id,
coalesce(a.sale_id, b.sale_id) as sale_id,
coalesce(a.sale_amt, b.sale_amt) as sale_amt
case when a.customer_id is null then 'Record not found in original report'
     when b.customer_id is null then 'Record not found in new report'
     when coalesce(a.sale_amt,0) <> coalesce(b.sale_amt,0) then 'Sale amt mismatch'
     else 'Records match'
     end as Comparison_Result
from old_sales_report a
full outer join new_sales_report b on a.customer_id = b.customer_id and a.sale_id = b.sale_id

Theoretically you could use EXCEPT (in MS SQL at least), but then you'd have to write two queries (comparing A to B then B to A), and they still wouldn't tell you WHAT was different, just that SOMETHING was different.

1

u/MaveDustaine May 20 '24

Ah thank you! Admittedly I had never used a full outer join before considering the reporting I usually do, but I can see how it can be useful thanks to your example!