CODEWITHSUNDEEP
sql-serversubquery
Brian Mayer
Brian Mayer

Reputation: 36

SQL Server ALL subquery with no rows, how to force false?

I'm grasping for syntax that would get me the performance of ALL, while excluding the parent rows with no children (failed subquery.)

CREATE TABLE #Parts 
(
    Part varchar(64) not null
)

INSERT INTO #Parts VALUES('Test One')
INSERT INTO #Parts VALUES('Test Two')
INSERT INTO #Parts VALUES('Test Three')

CREATE TABLE #Attr 
(
    Part varchar(64) not null,
    Attr varchar(8) not null
)

INSERT INTO #Attr VALUES('Test One','123P4567')
INSERT INTO #Attr VALUES('Test One','987P4567')
INSERT INTO #Attr VALUES('Test Two','123P4567')
INSERT INTO #Attr VALUES('Test Two','987X4567')

SELECT * 
FROM #Parts p
WHERE 'P' = ANY (SELECT SUBSTRING(a.Attr, 4, 1) 
                 FROM #Attr a 
                 WHERE a.Part = p.Part)

SELECT * 
FROM #Parts p
WHERE 'P' = ALL (SELECT SUBSTRING(a.Attr, 4, 1) 
                 FROM #Attr a 
                 WHERE a.Part = p.Part)

SELECT * 
FROM #Parts p
WHERE 'P' = ALL (SELECT SUBSTRING(a.Attr, 4, 1) 
                 FROM #Attr a 
                 WHERE a.Part = p.Part)
  AND EXISTS (SELECT a.Part FROM #Attr a WHERE a.Part = p.Part)

When I use ALL and EXISTS together (third query) in my large data set, the query goes from milliseconds to 60 seconds. Is there some way to alter the ALL syntax to give false on the failed subquery for 'Test Three'?

This related question helped me understand why the second query gives results other than I expected:

ALL vs ANY

Upvotes: 1

Views: 50

Answers (1)

Sander
Sander

Reputation: 4042

You could rework the ALL to a NOT EXISTS.

Instead of saying:

all related records must have 'P' as 4th character

You could say:

no related records can have anything different than 'P' as 4th character

The output of the implementation below matches the output of your third (desired) result query. Performance validation is much harder with your full data set.

select *
from #Parts p
where exists (  select 'x'
                from #Attr a
                where a.Part = p.Part)
  and not exists (  select 'x'
                    from #Attr a
                    where a.Part = p.Part
                      and substring(a.Attr, 4, 1) <> 'P' )

Upvotes: 1

Related Questions