Most of the interviewers I have seen tend to prefer people with better problem solving skills rather than better knowledge on a particular technology. No wonder considering the fact that technologies change with each fleeting moment, while a good problem solving mentality stays forever. With this prelude, I put forward this question that I came to hear from one of friends recently – How do you perform a right outer join with a left outer join clause? And no, you can’t swap the tables here. Disclaimer : This question is purely to test your knowledge of how the said Joins work and to test your problem solving skills. It may not serve any real purpose in a practical project scenario. Question Lets say, I have the following tables Demo and Demo2 Query : select * from Demo Output 
Query : select * from Demo2 Output 
Now using a left outer join, the result should be equivalent to the output of SELECT A.attribute,
B.attribute
FROM Demo2 AS A
RIGHT OUTER JOIN
Demo AS B
ON A.attribute = B.attribute; Output 
And you can’t swap the order of the tables i.e. Demo2 should be on the left hand side and Demo should be on the right side. Answer Now this exercise is basically to inculcate the importance of problem solving and hence I will try to detail out the steps as much as possible. Lets go step by step:- 1) The thing to understand here is that we can’t use the left outer join with the normal join condition like A.attribute = B.attribute, the reason being that we would get all the rows of the left side table (Demo2). Eg: SELECT A.attribute,
B.attribute
FROM Demo2 AS A
LEFT OUTER JOIN
Demo AS B
ON A.attribute = B.attribute; Output 
So somehow we need to bring the required rows in. 2) Now that we have understood that the above join condition won’t work, we need to think of another join condition. Let’s say, what would happen if we give a condition that is always true on the ON clause, something like 1=1? SELECT A.attribute,
B.attribute
FROM Demo2 AS A
LEFT OUTER JOIN
Demo AS B
ON 1=1; Output 
Cool, now we have got a cross product. 3) Now the thing to do is to understand what a right outer join is really. If the column on the right hand side matches with the column on the left hand side based on the join condition, then both the columns are displayed. Else, a NULL will be displayed on the left hand side with the correct column on the right hand side. For this first we can write a subquery in the columns like shown below SELECT (SELECT attribute
FROM Demo2 AS c
WHERE c.attribute = b.attribute) AS attr,
B.attribute
FROM Demo2 AS A
LEFT OUTER JOIN
Demo AS B
ON 1 = 1; Output 
4) Now all we need to do is to take the distinct and then we get the output as required. SELECT DISTINCT (SELECT attribute
FROM Demo2 AS c
WHERE c.attribute = b.attribute) AS attr,
B.attribute
FROM Demo2 AS A
LEFT OUTER JOIN
Demo AS B
ON 1 = 1; Output 
So, all is well? Not really. This particular query would break when we get duplicates. Suppose the TH row was duplicated in Demo table. Now, when we take the distinct after applying the subquery on the cross product, we will still get only one row for TH when a right outer join would have given 2 rows. So I came up with the below query to solve this particular scenario:- SELECT attr,
attribute
FROM (SELECT row_number() OVER (PARTITION BY CASE WHEN A.attribute = b.attribute THEN A.attribute ELSE NULL END, b.attribute ORDER BY a.attribute) AS rnk,
CASE WHEN A.attribute = b.attribute THEN A.attribute ELSE NULL END AS attr,
B.attribute,
row_number() OVER (PARTITION BY b.attribute ORDER BY CASE WHEN A.attribute = b.attribute THEN A.attribute ELSE NULL END DESC) AS rnk2
FROM Demo2 AS A
LEFT OUTER JOIN
Demo AS B
ON 1 = 1) AS Outr
WHERE (Outr.rnk = 1
AND Outr.rnk2 = 1)
OR (attr = attribute); Please post your queries in the comments if you have got alternate solutions, I would be pleased to have a look at them.
Performing a Right Outer Join with a Left Outer Join clause
