Simple SQL to check if a parent has any child rows or not

Question

I show a grid with parent data and should show an icon if it has matching child rows. My DB is in SQL Server 2008. Let me simplify, I have the following two tables -

Order (PK: ID)

File (PK: FileID, FK: OrderID)

An Order may have zero or more files associated with it. The File table has an OrderID column that contains an FK link to Order . Now I am showing a grid in which all Orders are listed, and I want to display an icon image indicating whether this Order child lines (files) or not.

Here is the tricky way I experimented, but not sure how efficient / scalable it is -

 SELECT DISTINCT o.ID, o.OrderNum, ... ,(CASE f.ID/f.ID WHEN 1 THEN 1 ELSE 0 END) as FilesExist ... FROM Order AS o LEFT OUTER JOIN dbo.FileHeader as f ON f.OrderID = o.ID 

The CASE statement works just fine as required. It will return 1 if one or more files exist, otherwise 0. If there are several lines of the file, it will try to repeat the Order line for which I added DISTINCT, and I do not select f.ID but f.ID/f.ID , which will be 1 (it exists) and 0 for null (does not exist). I found out that JOINs are better than inline SELECT COUNT(*) .

I tested and works, but I need expert opinion and I need to make sure that this is the best way to do this. This is a very simple example, but my SELECT is complex, as there are many queries and getting it will be expensive, so I need to make sure it scales.

Thanks.

EDIT. # 1: In conclusion - either it will be an internal SELECT using COUNT (*)

 SELECT c.ClaimNo,(SELECT COUNT(*) FROM dbo.FileHeader AS f WHERE f.ClaimID = c.ID ) AS FilesHExist FROM dbo.Claim AS c 

or the LEFT OUTER JOIN approach that I mentioned.

 SELECT DISTINCT c.ClaimNo, (CASE fh.ID / fh.ID WHEN 1 THEN 1 ELSE 0 END) AS FilesHExist FROM dbo.Claim AS c LEFT OUTER JOIN dbo.FileHeader AS fh ON fh.ClaimID = c.ID 

Attached are two images of the query execution plan. Please suggest which one is better.

Answer 1

To select multiple lines, this should be the fastest:

 SELECT o.ID ,o.OrderNum ,CASE WHEN EXISTS (SELECT * FROM dbo.FileHeader f WHERE f.OrderID = o.ID) THEN 1 ELSE 0 END AS FilesHExist FROM Order o; 

For SELECT, which includes large parts of dbo.FileHeader , this should work better:

 SELECT o.ID ,o.OrderNum ,CASE WHEN f.OrderID IS NULL THEN 0 ELSE 1 END AS FilesHExist FROM Order o LEFT JOIN (SELECT OrderID FROM dbo.FileHeader GROUP BY OrderID) f ON f.OrderID = o.ID 

First you need to group OrderID first, and then the left join. No need for DISTINCT at the end.

Never :

 (CASE f.ID/f.ID WHEN 1 THEN 1 ELSE 0 END) 

It opens you up to dividing by zero exception. Replace it with a check for NULL , as shown above.

Answer 2

If you can work with the amount of use childern

 SELECT o.ID ,o.OrderNum ,(SELECT COUNT(*) FROM dbo.FileHeader f WHERE f.OrderID = o.ID) AS FilesCount FROM Order o; 

Otherwise use

 SELECT o.ID ,o.OrderNum ,CASE WHEN (SELECT COUNT(*) FROM dbo.FileHeader f WHERE f.OrderID = o.ID) > 0 THEN 1 ELSE 0 END AS FilesExist FROM Order o; 

Recommendation:

Whenever you want to find out what is really happening in the database, compare the PLAN for a different version of the query - the assumption is at best ... PLAN shows you what you really will do (which takes, for example, how many rows it expects other things that we don’t know anything about when we answer your question on SO).

Assuming you are using SQL Server, this is available in SSMS ... the same goes for Oracle - there it is available in SQL Developer ... most databases have this option.

Answer 3

Try

 select A.Id OrderId ,case when isnull(B.FileCounts,0)>0 then 1 else 0 end FilesExist from [Order] A left join (select OrderId, COUNT(1) FileCounts from [File] group by OrderId)B on A.Id=B.OrderId