I had been requested this throughout among my interviews. Are you able to do JOIN using UNION keyword? Are you able to do UNION using JOIN keyword?
That's - 1. I ought to get same output as JOIN without needing JOIN keyword, but using UNION Keyword? 2. I ought to get same output as UNION without needing UNION keyword, but using JOIN Keyword?
Are you able to produce a good example of how to get this done if at all possible?
A job interview may be the framework which embark your wares. Recall: don't respond to questions )
Think about a press conference: the representative isn't searching to reply to difficult questions from journos to trap themselves out. Rather, they're searching for inquiries to which they have solutions, being the data they would like to release (with no more!)
Basically faced this within an interview, I'd utilize it to show my understanding of relational algebra because that is what I'd go in to the interview using the aim of doing I be alert for that "Discuss relational algebra here" question and this is it.
Loosely speaking, JOIN may be the counterpart of logical AND, whereas UNION may be the counterpart of logical OR. Therefore, similar questions using convention logic might be, "Are you able to do And taking advantage of OR?" and "Are you able to do OR using AND?" The solution would rely on what else you could utilize e.g. NOT might prove useful )
I'd be also enticed to go over the variations between your group of primative operators, the group of operators essential for computational completeness and also the group of operators and shorthands needed for practical reasons.
Attempting to answer the question directly boosts further questions. JOIN suggests 'natural join' in relational algebra whereas in SQL it suggests
INNER JOIN. When the question particularly pertains to SQL, is it necessary to answer for the
JOIN types? How about
To use an example, SQL's outer join is famouly a
UNION. Chris Date expresses it much better than I possibly could ever aspire to:
Outer join is specifically made to produce nulls in the result and really should therefore be prevented, generally. Relationally speaking, it is a type of shotgun marriage: It forces tables right into a type of union—yes, I actually do mean union, not join—even once the tables under consideration neglect to comply with the usual needs for union (see Chapter 6). It will this, essentially, by padding either from the tables with nulls before doing the union, therefore which makes them comply with individuals usual needs in the end. But there is no reason that padding should not be achieved with proper values rather than nulls
This is a great discussion point if, "I personally don't like nulls" is one thing you desired depict within the interview!
These are merely a couple of ideas that come to mind. The important point is, by asking these questions the interviewer is providing a branch. What's going to YOU hold on it? )
Because this is a job interview question, they're testing knowing about it of these two functions.
The likely answer they're expecting is "generally no you can't do that because they perform different actions", and also you would explain this in greater detail by proclaiming that a union appends rows towards the finish from the result set while a join adds further posts.
The only method you might have a Join along with a Union jobs are where rows contain data from only among the two sources:
SELECT A.AA, '' AS BB FROM A UNION ALL SELECT '' AS AA, B.BB FROM B
Is equivalent to:
SELECT ISNULL(A.AA, '') AS AA, ISNULL(B.BB, '') AS BB FROM A FULL OUTER JOIN B ON 1=0
Or to get this done with just one column in which the types match:
SELECT A.AA AS TT FROM A UNION ALL SELECT B.BB AS TT FROM B
Is equivalent to:
SELECT ISNULL(A.AA, B.AA) AS TT FROM A FULL OUTER JOIN B ON 1=0
One situation in which you would do that is for those who have data created over multiple tables but you need to see ti altogether, however would advise to utilize a UNION within this situation as opposed to a FULL OUTER JOIN due to the totally doing what you will otherwise expect.