THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Joins without JOIN

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2011/10/04/joins-without-join/

Published Tuesday, October 4, 2011 2:43 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

vijay kashyap said:

outer apply can be used for this below code

SELECT *

   , (SELECT COUNT(*) FROM Production.Product AS p

      WHERE p.ProductSubcategoryID = s.ProductSubcategoryID) AS ProdCount

FROM Production.ProductSubcategory AS s;

October 4, 2011 6:53 AM
 

Rob Farley said:

Indeed it can. I figured it wasn't necessary to show it though, as I discussed APPLY plenty in a previous post.

October 4, 2011 7:24 AM
 

Todd Everett said:

Thanks for this post Rob.  I've always wondered what those anti joins were.  Coming from a background of always using correlated EXISTS and NOT EXISTS I had only recently realized EXISTS is just INNER JOIN.  I never made the leap that NOT EXISTS is of course outer join with a WHERE filter so thank you for showing that.  Where I work folks like EXISTS and NOT EXISTS because it makes it clear what you are doing logically where writing as a join makes that a little more ambiguous.  I was just recently tending to use the inner join instead of EXISTS as I thought it performed better.  Now I see that may not be the case.  Thanks for rounding out my understanding of this topic!

October 4, 2011 8:26 AM
 

Brad Schulz said:

I love that we both touched on the OUTER + IS NULL inefficiency. :)

http://bradsruminations.blogspot.com/2011/10/t-sql-tuesday-023-flip-side-of-join.html

--Brad

October 4, 2011 9:10 PM
 

Rob Farley said:

Hi Todd,

EXISTS isn't actually an Inner Join, it's a Semi Join. Slight difference... an Inner Join will project rows onto each match. A Semi Join simply filters based on existence.

Hope this helps...

Rob

October 5, 2011 1:23 AM
 

Kawahee said:

What is the number of rebinds on the operators at the bottom of the  NOT EXISTS plan? If there is rebinding then it will be slower than the LEFT JOIN, which should bind to the table once.

Correlated subqueries are bad for performance because of rebinds, no?

October 9, 2011 12:54 AM
 

Kawahee said:

Just an update, I looked at the query plans myself and they seem to be equivalent in performance - both had the same number of (re)binds. I added a few more predicates to the WHERE and ON clauses to try and get the query optimizer to revert to rebinding for each record but both plans had the same number of rebinds.

The LEFT JOIN was actually slower, by a small margin, once I'd added the extra predicates.

I also changed the last query from SELECT * to SELECT s.*, since it's only fair we ask for the same data each time.

I'm going to keep looking at this.

October 9, 2011 3:06 AM
 

Chirag said:

Hi Rob,

Nice work , just loved your article. Your post give so much clarity to the topic/concepts.

thanks for posting.  

June 10, 2016 1:27 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement