Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Sub query expressions vs. Joins. Expand / Collapse
Author
Message
Posted Wednesday, July 21, 2010 9:14 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 6:51 AM
Points: 244, Visits: 543
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 -
Post #956904
Posted Wednesday, July 21, 2010 9:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 16, 2010 7:02 PM
Points: 1, Visits: 6
[x] Reside
Post #956907
Posted Wednesday, July 21, 2010 11:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:02 PM
Points: 1, 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
Post #956926
Posted Thursday, July 22, 2010 1:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 4, 2011 6:16 AM
Points: 15, 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.
Post #956960
Posted Thursday, July 22, 2010 2:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 7:34 AM
Points: 1, 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
Post #956997
Posted Thursday, July 22, 2010 2:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 4, 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.
Post #957007
Posted Thursday, July 22, 2010 3:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:57 AM
Points: 406, Visits: 773
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
Post #957026
Posted Thursday, July 22, 2010 4:02 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, August 11, 2014 3:57 AM
Points: 406, Visits: 773
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
Post #957040
Posted Thursday, July 22, 2010 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 12:20 PM
Points: 1, Visits: 46
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
Post #957047
Posted Thursday, July 22, 2010 4:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 4, 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 ?
Post #957048
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse