Logical Join and Physhical Join

Question

Logical Join and Physhical Join

Deepak Burkul

Mr or Mrs. 500

Points: 515
More actions
July 9, 2014 at 11:16 pm

#397035

What is the difference between Logical Join and Physical Join and there Types?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 1

Physical joins are the ones, which will be used by sql Server internally. You will not write queries with them unless you explicitly specify with Hints.

Logical joins: Inner join, Outer join, Full join, Cross join, Apply operators

Physical Joins: Merge join, Hash join

Thanks

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 2

And Loops Join.

But what's the real question? Is this an interview question, or are you looking to solve a problem?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Deepak Burkul Mr or Mrs. 500 Points: 515 More actions · Answer 3

Thank you......

yes this is interview question and i am going to learn more on execution plan of SQL server that time i get this confusion about Physhical Join and Logical Join.....

IS any one knows of the Physhical Join work?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The physical joins are the actual implementation options that SQL has. You say INNER JOIN or LEFT OUTER JOIN or whatever other logical join you need in the query, the optimiser then decides which of the physical join algorithms is going to me more efficient to use.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 5

Deepak Burkul (7/11/2014)
Thank you......
yes this is interview question and i am going to learn more on execution plan of SQL server that time i get this confusion about Physhical Join and Logical Join.....
IS any one knows of the Physhical Join work?

Using Physical join, SQL Server database engine actually join two record sets & display/retrieve the data.

As per your query, database engine will decide & use required physical join operator.

Thanks

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 6

Deepak Burkul (7/11/2014)
Thank you......
yes this is interview question and i am going to learn more on execution plan of SQL server that time i get this confusion about Physhical Join and Logical Join.....
IS any one knows of the Physhical Join work?

I'd recommend a couple of books. Itzik Ben Gan's T-SQL Querying covers a lot of the logical and physical processing of queries in the engine. Then there's Benjamin Nevarez's book on the Query Optimizer (which you can download an e-book for free) Finally there's my book on execution plans. You can download that for free or purchase it from the link in my signature. Between the three of them, you should have a pretty good understanding of how joins are generated.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Logical Join and Physhical Join

Cookies on SQLServerCentral