Skip to content
Advertisement

How do I make aggregate query return empty set instead of NULL row?

I have a SQL query like this:

SELECT t1.name, MAX(t2.value)
FROM t2
JOIN t1 ON t1.id = t2.t1_id
WHERE t2.t1_id = 1 AND t2.text_id = 16;

However, when t2 selection is empty, it returns a row containing NULL values (because of MAX function returning NULL when called on an empty set). I would like it to return an empty set instead. How can I achieve it?

Advertisement

Answer

Try this in sql server …

with cte as
(
SELECT t1.name, MAX(t2.value) a
FROM t2
JOIN t1 ON t1.id = t2.t1_id
WHERE t2.t1_id = 1 AND t2.text_id = 16;
)
select * from cte where a is not null

try this in Mysql

  select p.* from 
(
    SELECT t1.name, MAX(t2.value) a
    FROM t2
    JOIN t1 ON t1.id = t2.t1_id
    WHERE t2.t1_id = 1 AND t2.text_id = 16;
) p where p.a is not null

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement