The following code generate series of numbers starting from 1
select
row_number() over (order by s1.name) as number
from
sys.objects as s1 cross join sys.objects as s2
The resultset will have N*N rows where N is number of rows of catalog view sys.objects. If the rows of this view is 120, the resultset will have 120*120=14400 rows. So we will have a number table with values ranging from 1 to 14400.
![cross-join-result cross-join-result](http://lh3.ggpht.com/-_pb64jsTwA0/UPU5t6Um46I/AAAAAAAAAbA/233sTD241_U/cross-join-result%25255B4%25255D.png?imgmax=800)
The same functionality can be simulated without using CROSS JOIN
Method 1. Using Old Style Non-Ansi JOIN without WHERE clause
select
row_number() over (order by s1.name) as number
from
sys.objects as s1 , sys.objects as s2
Because there is no WHERE clause, it becomes CROSS JOIN producing N*N rows
Method 2. Use INNER JOIN with no column matching
select
row_number() over (order by s1.name) as number
from
sys.objects as s1 inner join sys.objects as s2
on 1=1
Because no columns are JOINed and 1=1 is always true, the above INNER JOIN will become a CROSS JOIN and produce N*N rows
![cross-join-result cross-join-result](http://lh6.ggpht.com/-GCUGlw488Ik/UPU5u4npKgI/AAAAAAAAAbE/vx4y6Mvubpw/cross-join-result%25255B8%25255D.png?imgmax=800)
Similarly LEFT and RIGHT JOINs with no column matching will behave the same like method 2
No comments:
Post a Comment