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

Question

I show a grid with parent data and need to show icon if it has a relevant child row(s) exist. My DB is in SQL Server 2008. Let me simplify, I've the following two tables -

Order (PK : ID)

File (PK: FileID, FK: OrderID)

An Order can have zero or more files related to it. The File table has a column OrderID which holds FK reference to Order. Now, I'm displaying a grid which lists all the Orders and I want to display an icon image indicating if that Order has any child rows (files) or not.

Here's a tricky way I've 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 seems to work perfectly as required. It'll return 1 if one or more file(s) exists, else 0. If multiple file rows exist then it will try to repeat the Order row for which I've added the DISTINCT and I'm not selecting f.ID but f.ID/f.ID which will be 1 (it exists) and 0 for null (doesn't exist). I've learned that JOINs are better than inline SELECT COUNT(*) statements.

I've tested and it works but I need expert opinion and need to make sure that this is the best way of doing it. This is a very simple example but my SELECT statement is complex as there are many lookups and its going to be a costly fetch so I need to make sure its scalable.

Thank you.

EDIT #1: To conclude - either it is going to be internal SELECT with a 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've 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 two images of query execution plan. Kindly suggest which one is better.

Answer 1

For selecting few rows, this should be 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 a SELECT that includes large portions of dbo.FileHeader this should perform 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

It should be cheaper to group OrderID first and then left join. No need for DISTINCT at the end.

Never use:

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

It opens you up to division by zero exception. Replace it with a check for NULL like demonstrated above.

Answer 2

If you can work with the number of childern use

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;

A recommendation though:

Whenever you want to know what really happens in the DB compare the PLANs for the different version of the query - everything is at best an educated guess... the PLAN shows you what your really will do (which takes into for example how many rows it expects to be returned and other stuff we don't anything about when answering your question on SO).

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