SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sub query expressions vs. Joins.


Sub query expressions vs. Joins.

Author
Message
cmapowers
cmapowers
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 27
Thank you.
IG
IG
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 3
Why not the following?

SELECT a.EmployeeID,a.Salary a.RActive
b.ManagerName
FROM Employees a (nolock)
,Managers b (nolock)
WHERE b.ManagerID = 1
AND a.ManagerID = b.ManagerID;



Gianluca Sartori
Gianluca Sartori
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10144 Visits: 13351
IG (1/20/2012)
Why not the following?

SELECT a.EmployeeID,a.Salary a.RActive
b.ManagerName
FROM Employees a (nolock)
,Managers b (nolock)
WHERE b.ManagerID = 1
AND a.ManagerID = b.ManagerID;


NOLOCK can return inconsistent data (dirty reads, non repeatable reads and the like), but can also return duplicate data or no data at all for some rows. NOLOCK means that data is not locked at all, even from the query itself, that could end up reading the same row twice in case of a page split.

If you are ok with inconsistent data, then go on and use NOLOCK, just make sure your users are ok with inconsistent data as well.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
SQLBoar
SQLBoar
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 197
I pity any already confused developer who reads this article. As, despite the title, it isn't about sub-queries. What the author refers to are called Derived Tables, Table Expressions (which is where Common Table Expressions get their name from) or sometimes (almost equally confusingly) Inline Views. Any clues - the execution plan has no SEMI-JOIN in it. Refer to Chapter 6 "Subqueries, Expressions and Ranking Functions" of the IBG SS2008 T-SQL Querying book for reference.
Sorry to be negative, but one of Paul Randall's top myths busted is "sub-queries are always slower". And, this kind of confusing article just adds to the myth.
But, if it was titled Table Expressions vs Joins Exclamation

This DBA says - "It depends".
Mad Myche
Mad Myche
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 518
I can honestly say that I have never seen subqueries used as such, these queries have always been of the inner join variety. And judging by the discussion from months ago, this will be something I remember in the future.

Anyways, I have seen a variation of the "standard" inner join
SELECT    EmployeeID, Salary, RActive, ManagerName
FROM Employees
INNER JOIN Managers
ON Manangers.ManagersID = Employees.ManagerID
WHERE Employees.ManagerID = 3


In which the WHERE is eliminated and is replaced with using an AND on the JOIN portion

SELECT EmployeeID, Salary, RActive, ManagerName
FROM Employees
INNER JOIN Managers
ON Manangers.ManagersID = Employees.ManagerID
AND Managers.ManagerID = 3



On larger datasets I have seen better performance with the second version, but on smaller I see no performance advantage.

I did do a test on a Products:Categories relationship on one of the DBs I have mounted, where there most populated category had about 50 items. I used the subset and the 2 variations of the join on both an '08 box and a 2K instance.
The results were fairly equal, with the subset at 34% and the joins at 33% apiece. This was consistent between both server instances

Director of Transmogrification Services
jcb
jcb
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2880 Visits: 994
After some years of observation I found if 2 queries are similar the optimizer ill process both in a similar way.
It means its likely there's no difference in performance for most joins x sub-queries scenarios.

And also it's mean
If you found differente query plans it's likely the queries are different and that's the case here.

Put yourself in the compiler place.

You can read the second query as:
fetch manager-1 (a single clustered index seek)
now fetch me all employers managed by manager-1 (40k index seek)

First query reads as:
fetch me all employers managed by manager-1 (40k index seek)--same cost as the other way
and for each fetched employer seek if there's a manager for him and fetch it as well.
Since its not a strict inner join its possible there are rows where the id is null.

The compiler still try (futile) to optimize by using a lazy spool.


I cannot test by now but I guess a FK can improve the subquery.
UKGav
UKGav
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 251
I had to use subqueries for 1-to-many tables where I needed to only return only 1 row for each record (Select top 1 date from InnerTable joined to OuterTable order by date desc) as [LastDate], thereby the results become 1-to-1.

This was noticable slow; but I don't believe there is an alternative (*??*) with JOINs without either missing out records from the primary table where the is no join or returning too many rows (LEFT JOIN) with a lot of null values from forgein table.

Might CTE's be the answer instead? I keep meaning to learn about them..

- Gav.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search