Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
MSBI 49
#BI 39
BRH 39
SSRS 35
#MSBI 29
SQL Server 28
#SSRS 22
SSAS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

Performing a Right Outer Join with a Left Outer Join clause

Aug 25 2010 7:24AM by Jason Thomas   

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.

Tags: BRH, MSBI, Database, SQL Server, #BI, #SQL Server, #MSBI, #TSQL,


Jason Thomas
19 · 9% · 2997
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • this might be disqualified for the RIGHT OUTER JOIN...

    SELECT A.attribute, B.attribute FROM (SELECT A2.attribute, B2.attribute AS alternate FROM Demo2 AS A2 RIGHT OUTER JOIN Demo AS B2 ON A2.attribute = B2.attribute GROUP BY A2.attribute, B2.attribute) AS A LEFT OUTER JOIN Demo AS B ON A.attribute = B.attribute OR (A.attribute IS NULL AND A.alternate = B.attribute)

    commented on Aug 25 2010 12:31PM
    robertmcook
    3047 · 0% · 2
  • Another solution that does not use partitioning;

    SELECT DISTINCT --- convert all non-intersecting left-side values to NULL CASE a.attrib WHEN b.attrib THEN a.attrib ELSE NULL END 'left', b.attrib 'right' FROM @demo2 a LEFT OUTER JOIN @demo b ON --- keep where right is in left a.attrib = b.attrib --- keep where right is not in left OR NOT EXISTS (SELECT 1 FROM @demo2 x WHERE x.attrib = b.attrib);

    commented on Sep 7 2010 11:56AM
    Dennis Allen
    331 · 0% · 128

Your Comment


Sign Up or Login to post a comment.

"Performing a Right Outer Join with a Left Outer Join clause" rated 5 out of 5 by 1 readers
Performing a Right Outer Join with a Left Outer Join clause , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]