CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
Zhihar
Zhihar

Reputation: 1428

mysql: 0 instead of an empty table

I have a sql query (simplified example):

select 
    ifnull(count(table.id), 0) as count 
from 
    table
where
    table.id > 10000

If anything in the table was not found, as a result it displays an empty table, but I want to output 0.

How can this be done?

my decision (speed has decreased in 5 times!!!)

DROP TEMPORARY TABLE IF EXISTS xxx;
CREATE TEMPORARY TABLE xxx ENGINE = MEMORY AS (SELECT SQL_CALC_FOUND_ROWS ...);

IF (FOUND_ROWS() = 0) THEN
    SELECT 0 AS count;
ELSE
    SELECT * FROM xxx;
END IF;

it's work! but slowly :(

Sorry, speed is constant :) my mistake

results: the problem was solved by the use of SQL_CALC_FOUND_ROWS and FOUND_ROWS (thanks @Unknown User) but temporary tables... not sure about the optimality of such a decision

Upvotes: 0

Views: 275

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Just remove the group by. Also, the ifnull() is unnecessary:

select count(t.id) as `count`
from table t
where t.id > 10000;

You seem to want the count of ids that meet the condition. Your version would return a separate count for each id.

If you wanted a separate count for each id, you could do:

select id, count(t.id) as `count`
from table t
where t.id > 10000
group by id 
union all
select null, 0
from table t
where not exists (select 1 from table t2 where t2.id > 10000);

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can use case when for checking no value in table

 SELECT case when table.id is null then 0 
 else count(table.id) end as Count_NUM_ID FROM table
  where
 table.id > 10000

Upvotes: 1

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

This way:

select 
    COALESCE(count(table.id), 0) as count 
from 
    table
where
    table.id > 10000
group by table.id

Upvotes: 1

Kaushik
Kaushik

Reputation: 2090

You can use this query. This will give you total # of records with matching query. If there will be no matching results then it will give you 0 .

Reference

SELECT SQL_CALC_FOUND_ROWS ColumnName FROM TableName WHERE ColumnName IN ('');

SELECT FOUND_ROWS();

Upvotes: 1

Related Questions