Simple check for SELECT query empty result

sql

best-practices

performance

join

byAlex Kataev·Aug 27, 2024

Easily verify if a SELECT query returns any results with the EXISTS condition. As soon as a match is found, processing halts—maximizing query efficiency.

IF EXISTS (SELECT 1 FROM your_table WHERE your_conditions)
    SELECT 'Good News! Data Exists 😃';
ELSE
    SELECT 'Oh Snap! No Data Found 😥';

Remember to replace your_table and your_conditions with details relevant to your dataset.

Quick Note: Better use SELECT 1 instead of SELECT * inside the EXISTS clause. There is no need to pull all column data only to check for existence. You got efficiency, my friend!

How many rows exactly?

Right after executing your query, @@ROWCOUNT discloses the number of rows affected. Cool, right?

SELECT * FROM your_table WHERE your_conditions;
IF @@ROWCOUNT > 0
    SELECT 'Data Exists; @ROWCOUNT to back it up 🎉';
ELSE
    SELECT 'No Data; @ROWCOUNT can vouch for it 😢';

Heads Up: Working with large data sets? Consider ROWCOUNT_BIG() which can tackle row count beyond the maximum int value. Talk about thinking big! Remember, ISNULL function is not suitable for checking set emptiness.

Visualising the Result

Trying to get your head around checks for an empty SELECT query result set? Imagine you just cast a wide fishing net.

🎣 ➡️ 🌊 : SELECT * FROM table WHERE condition;

When you find no fish?

🎣 ➡️ 🌊: []  # Empty array = Empty net;

When you bag the day's catch?

🎣 ➡️ 🌊: [🐟, 🐠]  # Array with data = A good day of fishing!

And always check your haul before breaking out the frying pan.

IF EXISTS(SELECT 1 FROM table WHERE condition)
    -- We got fish to fry 🍳
ELSE
    -- Might be a 'pizza night' after all 🍕

Specific Conditions? No Problem!

Got specific conditions in your query? Fret not. Combine IF EXISTS with those conditions. Voila! You have a confirmation of a non-empty set.

IF EXISTS(SELECT 1 FROM service WHERE specific_condition)
    SELECT 'Specific condition met. Win! 💪';
ELSE
    SELECT 'Specific condition not met. Bummer 😶';

Double Whammy: IF EXISTS and @@ROWCOUNT

Ever had a query that needed to return a certain number of rows? Use a combo of IF EXISTS and @@ROWCOUNT. The best of both worlds, right?

SELECT * FROM your_table WHERE your_conditions;
IF EXISTS (SELECT 1 FROM your_table WHERE your_conditions) AND @@ROWCOUNT > expected_count
    SELECT 'Sufficient data. Already planning dinner 🥘';
ELSE
    SELECT 'Insufficient data. Uber Eats, here we come 😅';

Pro Tip: Fancy a dive into @@ROWCOUNT? SQL Server Books Online is your snorkeling guide.

Context Matters, So Choose Wisely

Ask yourself: is performance king, or you need the exact row count? Based on your specific needs, choose between EXISTS and @@ROWCOUNT. And as always experiment to find your perfect fit.

explain-codes / Sql / Simple check for SELECT query empty result

Linked

How can I change NULL to 0 when getting a single value from a SQL function?



Select a value where it doesn't exist in another table



Delete SQL rows where IDs do not have a match from another table



Postgres - How to check for an empty array



Fastest way to determine if record exists

