CODEWITHSUNDEEP
sqlsql-server
newprint
newprint

Reputation: 7136

What is SELECT NULL in SQL?

While reading SQL Server 2012 doc., I came across the following example:

USE AdventureWorks2012 ;
GO
SELECT DepartmentID, Name 
FROM HumanResources.Department 
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;

I haven't seen SELECT NULL before. What is it, and how it works in given example ? (Obviously, SELECT NULL returns NULL).

Upvotes: 0

Views: 181

Answers (2)

Matthew
Matthew

Reputation: 10444

SELECT NULL returns a single row, with a single column, containing NULL

You can verify this by running SELECT NULL in SSMS.

The purpose of it in the query you posted is to illustrate that EXISTS doesn't care about the content of its subquery, only that something is returned, in this case a row with NULL

This is in contrast to something like COUNT which does not consider NULL in it's aggregations

Consider this:

SELECT 
    1
WHERE
    EXISTS(SELECT NULL)
-- Returns 1

versus this:

SELECT 
    COUNT(r.c)
FROM
    (SELECT CAST(NULL AS int) AS c) AS r
-- Returns 0

Upvotes: 3

Shang
Shang

Reputation: 518

SELECT NULL is used along with WHERE EXISTS to show the exist status but not data (When you don't need it)

For example you can have:

Select something 
From a b
Where Exists (SELECT NULL 
              From C item
              on item.cID = b.cID)

Upvotes: 1

Related Questions