What is the use of select * from table1, table2

Posted by Webmaster under Sql Server on 8/13/2010 | Views : 18363 | Status : [Administrator] | Replies : 1
Asked from @sachinravat22 in Interview Questions

What is the use of

select * from table1, table2
and
select *,* from table1, table2.

Best regards,
Webmaster
http://www.dotnetfunda.com



Responses

Posted by: PandianS on: 8/13/2010 [Member] [MVP] Silver

Up
0
Down
Hi

We can take this example

ie:
Table1 (Col1, Col2) with 4 Records
Table2 (Col11, Col22, Col33) with 3 Records

when you use the query given below, It will produce NxM number of rows (Cartesian Join)
select * from table1, table2

The result and column sequence from both tables would be given below with 4 x 3 = 12 Records.
Col1,Col2, Col11, Col22, Col33

when you use the query given below, It will also produce NxM number of rows (Cartesian Join), But each * represents N number of set of columns..
select *,* from table1, table2

The result and column sequence from both tables would be given below with 4 x 3 = 12 Records and repeats the same set of columns N number of time.
Col1,Col2, Col11, Col22, Col33, Col1,Col2, Col11, Col22, Col33

Note:
* - Single set of columns from both tables
*,* - Two times repeating the same set of columns from both tables
*,*,* - Three times repeating the same set of columns from both tables
...
...
...

Usage:
If you want to generate huge records for performance and load testing. you can go for Cartesian Join to produce these kind of multiplying records.
ie: select * from table1, table2

If you want to generate huge records with huge columns... (Only with result set, You can not create a new table with these set of columns, because the column names repeats)
ie: select *,*,*,* from table1, table2

So, the result will repeats the same set of columns 4 times

Cheers


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Webmaster, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response