September 26, 2012 at 5:18 am
Thanks for the question Amit. Surprised that 19% (as of now) got this wrong. I can see not getting some of the less frequently used features of T-SQL. But, joins are basic database theory. How can you get that wrong?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
September 26, 2012 at 6:09 am
This was removed by the editor as SPAM
September 26, 2012 at 6:15 am
If all the rows get matched then the query will act as a cross join.
I think the explanation is incomplete and inaccurate.
INNER JOIN returns number of rows from left table multiplied with number of rows from right table and that match.
So, 5x5 = 25.
September 26, 2012 at 6:45 am
Thank you thank you for the easy question today. I needed a pick me up. 🙂
September 26, 2012 at 6:52 am
Carlo Romagnano (9/26/2012)
If all the rows get matched then the query will act as a cross join.
I think the explanation is incomplete and inaccurate.
INNER JOIN returns number of rows from left table multiplied with number of rows from right table and that match.
So, 5x5 = 25.
I prefer this answer, although he did say 'act', i think he just meant it will 'return the same as'.
Also, syntax wont work in 2005, this would need separate INSERTS for each row, so I think the question was written with 2008 in mind but unspecified.
September 26, 2012 at 7:16 am
cartesian products.
Archimedes was a DBA!
September 26, 2012 at 7:39 am
Nice Question....
Best,
Naseer Ahmad
SQL Server DBA
September 26, 2012 at 8:25 am
Thanks for the back to basics question Amit!
September 26, 2012 at 8:31 am
rossss (9/26/2012)
[...] I think the question was written with 2008 in mind but unspecified.
2008, 2008 R2, or 2012... 😉
September 26, 2012 at 9:16 am
For a deeper understanding of what's going on, try this code:
CREATE TABLE #TABLE1
(
Col1 INTEGER
,Desc1 char(3)
)
CREATE TABLE #TABLE2
(
Col2 INTEGER
,Desc2 char(3)
)
INSERT INTO #TABLE1 VALUES (1, '1-1'), (1, '1-2'), (1, '1-3'), (1, '1-4'), (1, '1-5')
INSERT INTO #TABLE2 VALUES (1, '2-1'), (1, '2-2'), (1, '2-3'), (1, '2-4'), (1, '2-5')
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
INNER JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
LEFT JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
RIGHT JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
FULL OUTER JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
CROSS JOIN #TABLE2 Tab2
and run with "Show Actual Execution Plan" on.
While there is no guarantee of row order without an ORDER BY clause, my instance (2008R2 Express) actually did return the rows for the LEFT and RIGHT joins in the logical order of processing. The results of the INNER and CROSS joins, on the other hand, both imply a right-to-left order of processing, while the results of the FULL OUTER join are ordered as if SQL Server ran a LEFT join and then appended any rows (in this case none) from a RIGHT join not already in the LEFT join results.
The execution plan backs up this interpretation of the FULL OUTER join, showing a LEFT join concatenated with a "Left Anti Semi" join of Tables 2 and 1 respectively (which would be a "Right Anti Semi" join of Tables 1 and 2 respectively.)
Looking at the execution plan of the INNER join and CROSS join (the two types mentioned in the answer), the interesting thing is that SQL Server ran the CROSS join as an INNER join with no predicate.
So rather than saying that the INNER join acts as a CROSS join in this situation, the converse may be more appropriate!
September 26, 2012 at 9:30 am
sknox (9/26/2012)
For a deeper understanding of what's going on, try this code:
CREATE TABLE #TABLE1
(
Col1 INTEGER
,Desc1 char(3)
)
CREATE TABLE #TABLE2
(
Col2 INTEGER
,Desc2 char(3)
)
INSERT INTO #TABLE1 VALUES (1, '1-1'), (1, '1-2'), (1, '1-3'), (1, '1-4'), (1, '1-5')
INSERT INTO #TABLE2 VALUES (1, '2-1'), (1, '2-2'), (1, '2-3'), (1, '2-4'), (1, '2-5')
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
INNER JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
LEFT JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
RIGHT JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
FULL OUTER JOIN #TABLE2 Tab2
ON Tab1.Col1 = Tab2.Col2
SELECT Tab1.Col1, Tab2.Col2, Tab1.Desc1, Tab2.Desc2
FROM #TABLE1 Tab1
CROSS JOIN #TABLE2 Tab2
and run with "Show Actual Execution Plan" on.
While there is no guarantee of row order without an ORDER BY clause, my instance (2008R2 Express) actually did return the rows for the LEFT and RIGHT joins in the logical order of processing. The results of the INNER and CROSS joins, on the other hand, both imply a right-to-left order of processing, while the results of the FULL OUTER join are ordered as if SQL Server ran a LEFT join and then appended any rows (in this case none) from a RIGHT join not already in the LEFT join results.
The execution plan backs up this interpretation of the FULL OUTER join, showing a LEFT join concatenated with a "Left Anti Semi" join of Tables 2 and 1 respectively (which would be a "Right Anti Semi" join of Tables 1 and 2 respectively.)
Looking at the execution plan of the INNER join and CROSS join (the two types mentioned in the answer), the interesting thing is that SQL Server ran the CROSS join as an INNER join with no predicate.
So rather than saying that the INNER join acts as a CROSS join in this situation, the converse may be more appropriate!
I appreciate the nice easy one on a day when my brain is already fried!
Good explanation sknox. Very well done and thanks!
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
September 26, 2012 at 10:27 am
Ha. Well...I got this one wrong! How embarrassing!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 26, 2012 at 10:34 am
add the following for an interesting result:
union select 1,1
September 27, 2012 at 8:43 am
This is great basics. I'm learning this in my DB class. He mentioned how Cartesian products in the real world can be very scary. Good query to know.
September 27, 2012 at 11:06 pm
Nice Question. I think it’s not easy. First I thought that it should return 5 rows. Then I observed all rows contain 1.so I given answer for 25.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply