Conditional Column Join in SQL

Sometimes in the world of legacy enterprise systems you're tasked with what sounds exactly like a problem on Code Signal or similar platforms. The task I ran into today went a little bit like this:

Given write a query to pull all rows/columns from the tables below where numbers and letters match. If a letter is null include all combinations of colors with shapes in the output.

The first thing that came to mind was a join of the two tables on the letters and number columns:

Which resulted in:

Hey, the number 1 and letters a and b look good! But where did our number 2's go? Ah yes, the letters are null for 2's in Table1 so they would be dropped when joined to Table2 whose 2's have letters.

So let's try that again, but forget joining on letters:

And the result:

Yea, the 2's are back! Hmmm, we seem to have a lot of rows...looks like every combination of shape, color, and letter which is great for 2's but not so good for 1's.

So what we really need is to join on number and letter for 1's and number only for 2's. Enter the conditional column join.

A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join.

A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value. It's common to choose 1 or 0 (true/false) as the resulting value.

We then check if the output of the case statement is equal to 1 (the "end = 1" part of the join), if so the join between the two rows takes place, if not the row is dropped.

So in this case :) the case statement does the following:

• Check if Table1's letter column is null.
  • If yes we're going to keep the row (in reality return the value 1 which equals 1).
• If Table1's letter column is not null then check if Table1's letter column equals Table2's letter column.
  • If yes we keep the row and if not we drop the row.
• If neither were true we drop the row (return 0 which does not equal 1).

Basically all of that let's us join Table1 to the Table2 on just the number column if Table1's letter column is null and join on number and letter if Table1's letter column is not null.

Which results in our final answer:

Try it out in SQL Fiddle for some more practice!

Comments

[seimic]

you can have it shorter too

nvl(table1.letter, table2.letter) = table2.letter

[gschro]

That's much more succinct! Thanks for sharing!

[andreasneuman]

You know, I also was searching for the right way for working with SQL JOINS and have tried various solutions. I think SQL Complete is a pretty good tool

[Geshan Manandhar]

The power of SQL demonstrated. Good work!

[yairpe]

Good solution. Just what I was looking for
THANKS :)