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

Internal Select

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

My JOIN approach

Attached two images of query execution plan. Kindly suggest which one is better.

What type of SQL are you using ? Also it seems like a left outer join to `File` would yield `NULL` values, is this somehow handled by `dbo.FileHeader` with an `ISNULL()`? Further did you mean `f.ID/o.ID` ? As obviously `f.ID/f.ID` would *always* == 1? — Gibron, Nov 05 '11 at 07:54
My DB is in SQL Server 2008. My idea is to have 0 when the JOIN yields a NULL value. So, f.ID/f.ID will always be 1 if it exists and 0 if its NULL. That is what the CASE statement implies - it serves the purpose without having to include any intermediate SELECT * or COUNT statement. — Hemant Tank, Nov 05 '11 at 13:10
Query execution plan attached. Kindly let me know your final verdict. — Hemant Tank, Nov 21 '11 at 09:24

Erwin Brandstetter · Accepted Answer · 2023-08-04T15:01:07.683

16

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;

(I quoted the reserved word "Order".)

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.

edited Aug 04 '23 at 15:01

answered Nov 05 '11 at 08:01

Erwin Brandstetter

605,456
145
1,078
1,228

Query execution plan attached. Kindly let me know your final verdict. – Hemant Tank Nov 22 '11 at 09:11
Thanks. I understand but in my case f.ID is auto increment int so it can't be zero. And yes I'm going to have large db in future so I conclude that this is the best - "CASE WHEN f.OrderID IS NULL THEN 0 ELSE 1 END AS FilesHExist" – Hemant Tank Nov 23 '11 at 13:04

Yahia · Answer 2 · 2011-11-05T11:20:52.353

3

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.

edited Nov 05 '11 at 11:20

answered Nov 05 '11 at 09:08

Yahia

69,653
9
115
144

Thanks for your suggestion and I'll do that but at present my DB has too less records. Anyway, sometime ago I learned from SO that JOINs are less expensive then intermediate SELECT statements. I'm trying to accomplish it with simple JOIN and also avoiding GROUP BY. Finally I've used DISTINCT to get unique records. Pls suggest. – Hemant Tank Nov 05 '11 at 13:15
@HemantTank `DISTINCT` is often internally within the DB implemented as `GROUP BY`... `GROUP BY` by itself a'int bad at all and the "intermediate SELECT" is a bit ugly but nonetheless something one shouldn't abandon... Just saw that you are on SQL 2008 - check the execution PLANs of the different queries, even with a small set of records the PLAN outline the different paths the Db internally uses when you run those which in turn provides helpful hints. – Yahia Nov 05 '11 at 13:19
Query execution plan attached. Kindly let me know your final verdict. – Hemant Tank Nov 22 '11 at 09:10

Subhash Lama · Answer 3 · 2011-11-05T08:01:53.700

0

Try this

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

edited Nov 05 '11 at 08:01

answered Nov 05 '11 at 07:59

Subhash Lama

433
2
12

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

3 Answers3