Simple check for SELECT query empty result

Question

Can anyone point out how to check if a select query returns non empty result set?

For example I have next query:

SELECT * FROM service s WHERE s.service_id = ?;

Should I do something like next:

ISNULL(SELECT * FROM service s WHERE s.service_id = ?)

to test if result set is not empty?

What are you trying to do? What will you do next after making the check? — Mark Byers, May 21 '10 at 19:42
it is not clear if you want a result set returned and then check if any rows were int it, or if you just want to check if a query returns any rows without a result set?? — KM., May 21 '10 at 19:53

score 130 · Answer 1 · answered May 21 '10 at 19:33

130

IF EXISTS(SELECT * FROM service s WHERE s.service_id = ?)
 BEGIN
   --DO STUFF HERE

 END

answered May 21 '10 at 19:33

Ed B

6,028
3
26
35

5

Change 'SELECT *' to 'SELECT TOP 1 *' for efficiency – Ed B May 21 '10 at 19:49
20

@Ed B: It doesn't matter because EXISTS returns true on the first successful match. Test using `EXISTS (SELECT 1/0 FROM SERVICE...` - it should return a can't divide by zero, but it won't – OMG Ponies May 21 '10 at 20:04
1

@Ed B & @OMG Ponies, yea, I think SQL Server is smart enough to optimize the `EXISTS (SELECT *` or `EXISTS (SELECT 1` or `EXISTS (SELECT 1/0` away – KM. May 21 '10 at 20:08
2

@OMG Ponies, @KM - The only reason I tell my developers to use Select 1 is to simplify searches to squash uses of Select *. In addition, I have run into efficiency problems in databases other than SQL Server (cough Access cough) where it did something with the Select clause for some silly reason. – Thomas May 21 '10 at 20:37
1

I can note this is better than the accepted answer when you _only_ want to test if it is empty and not return a result set, such as if in a stored proc you do not want to return a result yet. – Mike M May 29 '20 at 00:10

score 103 · Accepted Answer · answered May 21 '10 at 19:37

103

Use @@ROWCOUNT:

SELECT * FROM service s WHERE s.service_id = ?;

IF @@ROWCOUNT > 0 
   -- do stuff here.....

According to SQL Server Books Online:

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.

answered May 21 '10 at 19:37

marc_s

732,580
175
1,330
1,459

7

This is quiet straight forward, I like it. And that's what I was looking for. Thanks. – Denys S. May 21 '10 at 20:37
It is worth noting that @@ROWCOUNT is only available in Transact-SQL – MatrixManAtYrService Sep 21 '15 at 19:35
1

@MatrixManAtYrService: yes - this question **IS** about SQL Server which uses T-SQL .... – marc_s Sep 21 '15 at 20:19
field value is NULL but @@ROWCOUNT return 1 ! Confusing :| – QMaster Apr 28 '18 at 10:26
1

Of course it returns 1! If there is a row being returned it doesn't matter what the row's columns look like. A column with null value can be valuable information. – Schnaps Jan 05 '21 at 10:07

score 14 · Answer 3 · answered May 21 '10 at 19:36

14

I agree with Ed B. You should use EXISTS method but a more efficient way to do this is:

IF EXISTS(SELECT 1 FROM service s WHERE s.service_id = ?)
BEGIN
   --DO STUFF HERE

END

HTH

answered May 21 '10 at 19:36

Raja

3,608
3
28
39

16

If you agree, you should vote for that answer. Rather than repost identical content... – OMG Ponies May 21 '10 at 19:38
1

@OMG Ponies: It's not identical. He changed the * to a 1. – Mark Byers May 21 '10 at 19:44
1

Well, using "(SELECT TOP 1 * ..." would be more efficient than using "SELECT 1..." – Ed B May 21 '10 at 19:48
5

I get identical query plans using `SET SHOWPLAN_ALL ON` for `IF EXISTS (SELECT * FROM ...` and `IF EXISTS (SELECT 1 FROM ...` – KM. May 21 '10 at 19:51
1

Identical results because RDBMS optimizes suboptimal query implicitly. For my taste, this answer is cleaner and doesn't depend on optimization. Nothing is done "behind the scene" and it doesn't require knowledge about query optimizations. – Harry Apr 09 '21 at 09:18

score 12 · Answer 4 · edited Dec 06 '17 at 21:29

12

You can do it in a number of ways.

IF EXISTS(select * from ....)
begin
 -- select * from .... 
end
else
 -- do something

Or you can use IF NOT EXISTS , @@ROW_COUNT like

select * from ....
IF(@@ROW_COUNT>0)
begin
-- do something
end

edited Dec 06 '17 at 21:29

answered Dec 06 '17 at 21:20

Samim Hussain

396
5
14

score 7 · Answer 5 · answered May 21 '10 at 19:38

7

try:

SELECT * FROM service s WHERE s.service_id = ?;

IF @@ROWCOUNT=0
BEGIN
    PRINT 'no rows!'
END

answered May 21 '10 at 19:38

KM.

101,727
34
178
212

score 4 · Answer 6 · answered May 21 '10 at 19:44

4

SELECT COUNT(1) FROM service s WHERE s.service_id = ?

answered May 21 '10 at 19:44

ovais.tariq

2,627
17
12

3

Any difference with count(*)? – liang May 10 '13 at 00:50
1

@liang, yes, no actual data is read from disk so it's faster. – Paul-Sebastian Manole Dec 22 '21 at 08:05

score 3 · Answer 7 · answered May 21 '10 at 19:40

To summarize the below posts a bit:

If all you care about is if at least one matching row is in the DB then use exists as it is the most efficient way of checking this: it will return true as soon as it finds at least one matching row whereas count, etc will find all matching rows.

If you actually need to use the data for processing or if the query has side effects, or if you need to know the actual total number of rows then checking the ROWCOUNT or count is probably the best way on hand.

score 2 · Answer 8 · edited Dec 06 '17 at 21:30

2

SELECT * FROM service s WHERE s.service_id = ?;
IF @@rowcount = 0
begin
select 'no data'
end

edited Dec 06 '17 at 21:30

answered Jun 09 '14 at 08:19

Viranja kaushalya

775
6
5

score 0 · Answer 9 · answered Feb 16 '15 at 12:07

0

In my sql use information function

select FOUND_ROWS();

it will return the no. of rows returned by select query.

answered Feb 16 '15 at 12:07

Sunil Kumar

655
1
7
12

score 0 · Answer 10 · answered May 21 '10 at 19:34

SELECT count(*) as count FROM service s WHERE s.service_id = ?;

test if count == 0 .

More baroquely:

select case when (SELECT count(*) as count FROM service s WHERE s.service_id = ?) = 0 then 'No rows, bro!' else 'You got data!" end as stupid_message;

csandreas1 · Answer 11 · 2019-02-11T16:31:15.237

0

SELECT count(*) as CountThis ....

Then you can compare it as string like so:

IF CHECKROW_RS("CountThis")="0" THEN ...

CHECKROW_RS is an object

edited Feb 11 '19 at 16:31

answered Feb 11 '19 at 14:24

csandreas1

2,026
1
26
48

score 0 · Answer 12 · answered May 20 '19 at 07:44

well there is a way to do it a little more code but really effective

$sql = "SELECT * FROM messages";  //your query
$result=$connvar->query($sql);    //$connvar is the connection variable
$flag=0;
     while($rows2=mysqli_fetch_assoc($result2))
    { $flag++;}
    
if($flag==0){no rows selected;}
else{
echo $flag." "."rows are selected"
}

Simple check for SELECT query empty result

12 Answers12

Linked