rkd rkd - 7 days ago 6
SQL Question

SQL - JOIN using UNION ?? UNION using JOIN?

I was asked this question during one of my interviews.
Can you do JOIN using UNION keyword?
Can you do UNION using JOIN keyword?

That is -
1. I should get same output as JOIN without using JOIN keyword, but using UNION Keyword?
2. I should get same output as UNION without using UNION keyword, but using JOIN Keyword?

Can you give me an example of how to do this if possible?

Answer

An interview is the framework on which you set out your wares. Remember: don't answer questions ;)

Think of a press conference: the spokesperson is not looking to answer difficult questions from journos to catch themselves out. Rather, they are looking for questions to which they already have answers, being the information they want to release (and no more!)

If I faced this question in an interview, I would use it to demonstrate my knowledge of relational algebra because that's what I'd have gone into the interview with the intention of doing; I be alert for the "Talk about relational algebra here" question and this would be it.

Loosely speaking, JOIN is the counterpart of logical AND, whereas UNION is the counterpart of logical OR. Therefore, similar questions using convention logic could be, "Can you do AND using OR?" and "Can you do OR using AND?" The answer would depend on what else you could use e.g. NOT might come in handy ;)

I'd also be tempted to discuss the differences between the set of primitive operators, the set of operators necessary for computational completeness and the set of operators and shorthands required for practical purposes.

Trying to answer the question directly raises further questions. JOIN implies 'natural join' in relational algebra whereas in SQL it implies INNER JOIN. If the question specifically relates to SQL, do you have to answer for all the JOIN types? What about UNION JOIN?

To employ one example, SQL's outer join is famously a UNION. Chris Date expresses it better than I could ever hope to:

Outer join is expressly designed to produce nulls in its result and should therefore be avoided, in general. Relationally speaking, it's a kind of shotgun marriage: It forces tables into a kind of union—yes, I do mean union, not join—even when the tables in question fail to conform to the usual requirements for union (see Chapter 6). It does this, in effect, by padding one or both of the tables with nulls before doing the union, thereby making them conform to those usual requirements after all. But there's no reason why that padding shouldn't be done with proper values instead of nulls

SQL and Relational Theory, 1st Edition by C.J. Date

This would be a good discussion point if, "I hate nulls" is something you wanted to get across in the interview!

These are just a few thoughts that spring to mind. The crucial point is, by asking these questions the interviewer is offering you a branch. What will YOU hang on it? ;)