SQL Server

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.

Ever since I wrote Converting multiple rows into a single comma separated row, I was trying to find a SQL command which will do the reverse, which is converting the single comma separated row back to multiple rows. I checked up a few blogs and I found out that it was possible to do with the help of custom functions or stored procedures, but I was not interested in all of them. Finally I got the answer that I was looking for, and now that I did, I did not waste precious time in noting it down here, lest I forget. Let me try to show you what we are trying to achieve here with the help of an image

This can be done with the help of the below query

 SELECT A.[State],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [State],  
         CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

Again, no stored procedures or function, just plain old SQL 🙂

Update (23/05/2010) : I saw this blog by Brad Schulz (MVP) explaining this scenario and the performance implications in detail. So people who are serious about getting this in their code, please read his blog before you do so - Delimited String Tennis Anyone?