CODEWITHSUNDEEP
sql-server
FAlvarez
FAlvarez

Reputation: 11

How to handle a subquery with no results?

I´m trying to build a query using a subquery in my WHERE condition, and when the subquery table is empty, I actually want to apply no filters to my query, and get all entries.

How can I achieve what I want?

This is my query:

SELECT 
m.Material,
p.Plant
FROM dbo.AllPlants AS p
JOIN dbo.AllMaterials AS m
ON p.MaterialID = m.MaterialID
WHERE p.Plant IN ( SELECT Plant FROM dbo.MyFavoritePlants);

If I have no Favorite Plants, I want All Plants!

Upvotes: 1

Views: 178

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You could add second condition to handle that scenario:

SELECT 
m.Material,
p.Plant
FROM dbo.AllPlants AS p
JOIN dbo.AllMaterials AS m
ON p.MaterialID = m.MaterialID
WHERE p.Plant IN (SELECT Plant FROM dbo.MyFavoritePlants)
   OR NOT EXISTS (SELECT 1 FROM dbo.MyFavoritePlants)

Upvotes: 2

Geezer
Geezer

Reputation: 497

I don't have enough reputation yet to comment but this might work

SELECT 
m.Material,
p.Plant
FROM dbo.AllPlants AS p
JOIN dbo.AllMaterials AS m ON p.MaterialID = m.MaterialID
LEFT JOIN dbo.MyFavoritePlants FP ON FP.Plant = P.Plant

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

Instead of sub query, you can use LEFT JOIN. So if there is no entry from MyFavoritePlants table, it will ignore the LEFT JOIN condition.

SELECT m.Material,
       p.Plant
FROM dbo.AllPlants AS p
JOIN dbo.AllMaterials AS m ON p.MaterialID = m.MaterialID
LEFT JOIN dbo.MyFavoritePlants FP ON FP.Plant = p.Plant

Upvotes: 0

Related Questions