what is the default join executed if we dont use any joins in a query? [Resolved]

Posted by Rajendra.prasad under Sql Server on 5/16/2010 | Views : 9223 | Status : [Member] | Replies : 3
Hi All,
what is the default join executed if we dont use any joins in a query?

Regards,
Rajendra




Responses

Posted by: Raja on: 5/16/2010 [Member] Starter

Up
0
Down

Resolved
That is called inner join.

For example

Select table1.*, table2.* from table1, table2 where table1.id = table2.id

This will be the inner join and common records from both tables will be displayed.

Thanks

Regards,
Raja, USA

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

Posted by: PandianS on: 5/17/2010 [Member] [MVP] Silver

Up
0
Down

Resolved
Hi

Good day

As Mr.Raja said, The default JOIN predicate is "INNER JOIN".

See for further clarification...

Query1:

SELECT * FROM TABLE1,TABLE2 WHERE TABLE1.ID = TABLE2.ID

1. Physical Operator of this query is : Hash Match (Join Algorithm)
2. Logical Operator of this query is : INNER JOIN (It will produces common record from the both tables)
3. Entimated Rows will be : Matching records only

Query2:

SELECT * FROM TABLE1, TABLE2

1. The Physical Operator of this query is : Nested Loop (Join Algorithm)
2. Logical Operator of this query is : INNER JOIN(It will produces Cartesian Product of the both tables)
3. Entimated Rows will be : N*M records ( Table1 records * Table2 records = Cartesian product)

So, In our both scenario, we are not used any JOIN predicates. But the default JOIN predicate is "INNER JOIN", But the JOIN algorithm differs.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

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

Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Default join is inner join

Type1:

select * from tablename1,tablename2 where tablename1.empid=tablename2.empid

Type2:

select * from tablename1 innerjoin tablename2 on tablename.empid=tablename2.empid

Hope this will help

regard

sriram

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

Login to post response