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
brian118
brian118
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1776 Visits: 602
Comments posted to this topic are about the item Sub query expressions vs. Joins.

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
miroslav 33997
miroslav 33997
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 6
[x] Reside
tim.napier
tim.napier
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 16
What happens if you use a left join in the second of the two queries. The fact they both returned 40000 rows is by coincidence, cos the sub select would returns if their isn't an equi joiin
crackbridge
crackbridge
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 48
Its interesting your article, one thing its not clear enought to me is why do you open your article with a GROUP and COUNT issue but later you develop a SELECT JOIN for 1-m topic ?

The first, taking case of GROUP BY, it would be iteresting to know how it works if you want to calculate for each ManagerId, two clumns, COUNT of workers with SALARY>1500 and COUNT of workers with SALARY<=1500, not using a CASE, but using a SUBQUERY to count vs. any other approach.

Related to the analysis of the execution plant its very helpful your comments and the bibliography you have referenced.

One last advice, not to use classic SELF linked case of EMPLOYEE-MANAGER as data sample since its not normalized as two tables but one table with references to it self.

Thanks,
L.
paul-876346
paul-876346
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 19
Thank you - I also wonder how the sub-query or join would compare to Cross (or Outer) Apply - as in the following query?

SELECT EmployeeID, Salary, RActive, CAM.ManagerName FROM Employees
CROSS APPLY (SELECT ManagerName FROM Managers WHERE Managers.ManagerID = Employees.ManagerID) CAM
WHERE Employees.ManagerID = 1
bayudw
bayudw
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 7
What about vice versa condition?

For example, I have two tables. First, a table for list of accounts. Second, a table for list of account's transactions.
I want to get the last transaction amount from second table for each of account in the first table.

How about that? Which one of those queries I might choose?

Thanks.
Arjun Sivadasan
Arjun Sivadasan
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2228 Visits: 985
Brian, Thanks for the article. I have a question here. I am not gonna post the DDL scripts as I have used your sample tables.

Consider the following statement that you had posted:


Select COUNT(*),ManagerID
From Employees
Group By ManagerID
Order By ManagerID



Now say, I need a third column which shows all the subordinates (IDs in this case) of a manager as csv. Can this be done in a better way than this?

select 
e.ManagerID,
COUNT(*) [No of Subs],
(select(stuff((select ',' + cast(EmployeeID as varchar)
from Employees where managerid = e.managerid
for xml path('')),1,1,''))) [SubIDs]
from Employees e
group By e.ManagerID
order By e.ManagerID



I know the requirement (showing IDs as csv) is a little weird but I had to get similar output in some reports. Please comment.

- arjun
Arjun Sivadasan
Arjun Sivadasan
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2228 Visits: 985
bayudw (7/22/2010)
What about vice versa condition?

For example, I have two tables. First, a table for list of accounts. Second, a table for list of account's transactions.
I want to get the last transaction amount from second table for each of account in the first table.

How about that? Which one of those queries I might choose?

Thanks.


I have assumed this schema. AccountID and TransactionID should be primary keys. AccountID should be a foreign key in #Transaction. You can create non-clustered indexes on #Transaction.AccountID and #Transaction.TransactionDate. Alternately, you can also use TransactionID column for orderby clause of row_number.

You should have posted this as a separate thread. I can't see any relation to the topic.

--I have two tables. First, a table for list of accounts. Second, a table for list of accounts transactions.
--I want to get the last transaction amount from second table for each of account in the first table.

create table #Account(AccountID int identity(1,1), AccountName char(2))
create table #Transaction(TransactionID int identity(1,1), AccountID int, TransactionAmount float, TransactionDate datetime)

insert into #Account
select 'A1' union
select 'A2' union
select 'A3'

insert into #Transaction
select 1, 145.50, dateadd(mi,-10,getdate())
insert into #Transaction
select 1, 125.50, getdate()
insert into #Transaction
select 2, 245.50, dateadd(mi,-10,getdate())
insert into #Transaction
select 2, 225.50, dateadd(mi,-5,getdate())
insert into #Transaction
select 2, 205.50, getdate()
insert into #Transaction
select 3, 345.50, dateadd(mi,-10,getdate())

select * from #Account
select * from #Transaction

;with cte as
(
select *,
row_number() over(partition by AccountID order by TransactionDate desc) [RN]
from #Transaction
)
select * from cte where RN = 1

drop table #Account
drop table #Transaction



- arjun
jainraj_2000
jainraj_2000
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 48
The Script is not working in SQL SERVER 2005 the populate tables procedure is having an insert into managers with multiple values.
In order to run it correctly it should be changed to

INSERT INTO Managers (ManagerName) VALUES ('Manager1');
INSERT INTO Managers (ManagerName) VALUES('Manager2');
INSERT INTO Managers (ManagerName) VALUES('Manager3');



Regards
Rajendra
bayudw
bayudw
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 7
Thanks for your replay.

I use this query below :

Select A.AccountID, A.OpenDate,
'Amount'=(Select Top 1 TransactionAmount From Transaction Where AccountID=A.AccountID order by TransactionDate desc)
From Account A

or

any idea else ?
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