CODEWITHSUNDEEP
sql-server
hemu_k
hemu_k

Reputation: 1

Return null if no records found in SQL Server

I have below query which is not returning any rows

SELECT branch, acno, custno 
FROM ac_master 
WHERE branch = 9

How do I return null as a result?

Expected result:

branch acno custno
-------------------
null   null null

Upvotes: 0

Views: 1896

Answers (2)

SQLpro
SQLpro

Reputation: 5131

The query that @larnu gives is faster from some other solutions like this one :

WITH 
T AS
(
SELECT branch, acno, custno 
FROM   ac_master 
WHERE  branch = 9
)
SELECT * FROM T
UNION  ALL
SELECT NULL, NULL, NULL
WHERE  NOT EXISTS(SELECT * FROM T);

If the cardinality of the result is low (generally under 10 000 rows).

I tested it over 1, 1000 and 1000000 rows and the query cost results are above ;

1 row : 40% @larnu 60% @sqlpro

1000 rows : 43% @larnu 57% @sqlpro

1000000 rows : 52% @larnu 48% @sqlpro

Upvotes: 0

Thom A
Thom A

Reputation: 95561

It's an odd format, but you could use a VALUES table construct with a LEFT JOIN:

SELECT m.branch,
       m.acno,
       m.custno
FROM (VALUES(NULL))V(N)
     LEFT JOIN dbo.ac_master m ON m.branch = 9;

This'll return a single row, of 3 NULLs, if the LEFT JOIN to ac_master returns no rows.

Though I don't disagree with DavidG's comment that this is an odd requirement.

Upvotes: 3

Related Questions