Pages

Tuesday, August 24, 2010

Performing a Right Outer Join with a Left Outer Join clause

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 Result - Demo

Query : select * from Demo2

Output

Query Result - Demo2

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

Demo2 Right outer join Demo

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

Demo2 left outer join Demo

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

join on 1=1

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

Subquery result

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

Demo2 Right outer join Demo

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.