When joining two or more tables, sometimes you end up in a scenario where values you are joining on just don’t quite match up. There are plenty of ways to resolve for this: a subquery with a CASE statement in the join statement for the table you are joining in, a CASE statement in a temp table where all values are changed to match, or this handy little trick of using a CASE statement in the JOIN’s ON clause.
Let’s take a quick look at using a regular join with no adjustment (obviously not the super cool party trick a couple of scrolls below it):
In the image above, we can see that when we try to LEFT JOIN our ‘cust_table’ to our super fancy capes ‘orders’ table, we end up with some Nulls when joining on ‘cust_num’. In the ‘orders’ table, we have instances where Count von Count and Papa Drac used our E-Comm ordering system, thus concatenating a nice little ‘-EC’ to the end of their ‘cust_num’. Their customer data located in the ‘cust_table’ doesn’t have our fancy new ‘-EC’ string.
I’m guessing that it’s not common knowledge, mostly because I can’t recall ever hearing this, but a CASE statement can be used in the ON portion of a JOIN statement, as shown below:
Let’s take a look at the results now:
Bleh bleh bleh! It worked!
There might be a time, and I speak from experience, where this can be a big time saver when updating an existing query. You might have other joins and whatnots going on that you don’t want to mess around with. All you really hope for is to join the table in correctly. This helps get you there!
SQL is a powerful and useful tool. There’s definitely more than one way to crack an egg/write code to do what you need it to do. This is just something you might want to add to your toolbox. Until next time, keep on learnin’!
Continue reading: https://towardsdatascience.com/a-case-statement-in-a-sql-join-13496723dca9?source=rss—-7f60cf5620c9—4