Best Method to Select Master Record With Not Detail Records 3

[Auguy]

SQL 2005. What is the best method of selecting only the master records that HAVE detail records? Think of this as invoice master and detail tables. Also the inverse, master records that HAVE NO detail records. I thought about using Exists In Select...., but that seems uncecesary. Could I use a left join on the detail table (Is Null, Is Not Null) and then group by the master PK to get the proper records? I only want one master record no matter how many detail records there are. This seems simple, just looking for some advice.

Auguy
Northwest Ohio

 

[markros]

select M.* from master M where exists (select 1 from Details where Details.Master_FK = M.Master_Pk)

For the NOT condition just use NOT EXISTS.

 

[Auguy]

Thanks Markros. So simple, no wonder I didn't think of it!

Auguy
Northwest Ohio

 

[PWise]

try joins I think it will work faster

for Having details

Select m.*
from master
inner join(Select distinct Details.Master_FK fk           from Details
           )D
on m.Master_Pk=D.fk

for not having details

Select m.*
from master
Left join(Select Details.Master_FK fk           from Details
           )D
on m.Master_Pk=D.fk
Where D.fk is null

 

[Auguy]

Thanks Pwise!

Auguy
Northwest Ohio

 

[markros]

LEFT JOIN with NULL will probably perform better than NOT EXISTS.

However, EXISTS and INNER JOIN are not equivalent and if the requirement is to get records that have details records, then you need to use EXISTS.

If you want to get master records with corresponding details records, then use INNER join.

 

[Auguy]

Thanks Markros. If you get a minute, for my education could you please explain why the Exists and Inner Join would yield different results as coded above?

Auguy
Northwest Ohio

 

[markros]

If you have 1 to many relationship, with INNER JOIN you will get as many records per Master.PK as you have Details records.

With the EXISTS check you're only checking for the existence of the details records, so you always get only one Master record.

Though, that code

Select m.*
from master
inner join(Select distinct Details.Master_FK fk           from Details
           )D
on m.Master_Pk=D.fk

will only return one record, but it uses derived table approach and distinct, so, as it's written, it is supposed to be slower - though if you have a chance to compare execution plans, I would appreciate it.

 

[PWise]

select M.* from master M where exists (select 1 from Details where Details.Master_FK = M.Master_Pk)

Select m.*
from master
inner join(Select distinct Details.Master_FK fk           from Details
           )D
on m.Master_Pk=D.fk

Will return the Same thing And i thingk joins wil be faster

 

[gmmastros]

In this case... the exists query will be faster.

In terms of performance, distinct is a relatively expensive operation. Also, in terms of performance, Exists is a relatively fast operation.

Comparing execution plans, the join version has an extra step:

[tt]
StmtText
-----------------------
Select Table1.Table1Name
From Table1
Where Exists(Select 1 From Table2 Where Table1.Table1Id = Table2.Table1Id)


StmtText
-----------------------------------------
|--Merge Join(Left Semi Join, MERGE[Table1].[Table1Id])=([Table2].[Table1Id]), RESIDUAL[Table1].[Table1Id]=[Table2].[Table1Id]))
|--Clustered Index Scan(OBJECT[DatabaseName].[dbo].[Table1].[PK_Table1_Table1Id]), ORDERED FORWARD)
|--Index Scan(OBJECT[DatabaseName].[dbo].[Table2].[Table2_Table1Id]), ORDERED FORWARD)


StmtText
--------------------------------
Select Table1.Table1Name
From Table1
Inner Join (Select Distinct Table1Id From Table2) As A
On Table1.Table1Id = A.Table1id


StmtText
-----------
|--Hash Match(Inner Join, HASH[Table2].[Table1Id])=([Table1].[Table1Id]), RESIDUAL[Table2].[Table1Id]=[Table1].[Table1Id]))
|--Stream Aggregate(GROUP BY[Table2].[Table1Id]))
| |--Index Scan(OBJECT[DatabaseName].[dbo].[Table2].[Table2_Table1Id]), ORDERED FORWARD)
|--Index Scan(OBJECT[DatabaseName].[dbo].[Table1].[Table1_Table1Name]))

[/tt]

Eventhough there is no group by statement, it appears as though SQL is implementing the distinct operation as though it were a group by. Also notice that the exists query is implemented through a left semi join where the join version is implemented through a has match.

The following 2 queries generate identical execution plans.

Select m.*
from master
inner join(Select [!]distinct[/!] Details.Master_FK fk           from Details
           )D
on m.Master_Pk=D.fk

Select m.*
from master
inner join(Select Details.Master_FK fk           from Details [!]Group By Details.Master_FK[/!]
           )D
on m.Master_Pk=D.fk

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

How did you grab these execution plans, George?

Thanks in advance.

 

[gmmastros]

SET SHOWPLAN_TEXT ON
go
Your Query Here

Also... Set your results to text

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Thanks.

 

[Olaf Doschke]

In SQL Server Management Studio you can also show the execution plan.

There are two toolbar buttons, one for the estimated execution plan, and one to include the real execution plan with the execution. The plan will be another tab besides the results and messages tab.

Bye, Olaf.

 

[markros]

Olaf,

I know that and also with this

http://blogs.lessthandot.com/index....ng/ssms-tools-pack-something-every-sql-serv-2

you can save the execution plans to the clipboard, however, I was curious how to be able to post plans in the simple text format.