How to Select an Empty Result Set

How to select an empty result set?

There's a dummy-table in MySQL called 'dual', which you should be able to use.

select
    1
from
    dual
where
    false

This will always give you an empty result.

How do I return an empty result set from a procedure using T-SQL?

If you want to simply retrieve the metadata of a result set w/o any actual row, use SET FMTONLY ON.

Simple select query returning empty result set?

You need backquotes instead of single quotes. Try:

SELECT * FROM `votes` WHERE `user_id` = 8 AND `image_id` = 5;

Single quotes are used for string constants. So the string "user_id" is not equal to the integer "8".

How to get empty result set with a CASE statement?

You can filter out NULL values in the outer WHERE clause.

SELECT CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END
WHERE CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END IS NOT NULL;

or

SELECT *
FROM (
        SELECT CASE WHEN ('test' IS NULL) THEN (SELECT 'null_result') ELSE (SELECT 'empty_result' WHERE 1<>1) END as result_case
) x
WHERE result_case IS NOT NULL;

How to get empty result set in MySQL query with aggregate function?

• To consider all of the "riders", despite having no distance travelled, you need to use Left Join with the driver table as your first table.
• Now, to calculate the total distance travelled by them, use Sum() with Ifnull() function to return 0 distance travelled instead of null. Due to left join, distance value will come up as null if there is no matching row found in the second table ride.
• Also, you should use Group By when using aggregate functions like Sum.

Try the following:

select d.name as 'name', 
       sum(ifnull(r.distance,0)) as 'sum_distance'
from driver d
left join ride r on r.driver_id = d.id
where r.end_time2 > '2018-09-25'
  and r.start_time2 < '2018-09-26' 
group By d.name 

How to return empty result for columns in a stored procedure

You can apply a trick like below-

DECLARE @RunDate date = '20190504'

IF DATENAME(WEEKDAY, @RunDate) NOT IN ('Saturday','Sunday')
BEGIN
    --Do the actual Logic
    --For Example
    SELECT * FROM your_table
END

ELSE
BEGIN
    --Do the tricks
    SELECT * FROM your_table
    WHERE 1= 2 -- This will never return a row!! But the column names :)
END

Simple check for SELECT query empty result

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.

How to check result set is empty in store procedure

You can use an IF NOT EXISTS:

IF NOT EXISTS (SELECT xmlRequest FROM Api_request
               WHERE Api_id = @Api_id)
BEGIN
   /*if result set empty*/
   /*  will add some logic here */
END

---

Related Topics