AdventureWorks Database: Returns TRUE even if NULL is specified in the subquery

Last update on October 11 2025 12:42:02 (UTC/GMT +8 hours)

145. From the following table write a query in SQL to return TRUE even if NULL is specified in the subquery. Return DepartmentID, Name and sort the result set in ascending order.

Sample table: HumanResources.Department

departmentid|name                      |groupname                           |modifieddate           |
------------+--------------------------+------------------------------------+-----------------------+
           1|Engineering               |Research and Development            |2008-04-30 00:00:00.000|
           2|Tool Design               |Research and Development            |2008-04-30 00:00:00.000|
           3|Sales                     |Sales and Marketing                 |2008-04-30 00:00:00.000|
           4|Marketing                 |Sales and Marketing                 |2008-04-30 00:00:00.000|
           5|Purchasing                |Inventory Management                |2008-04-30 00:00:00.000|
           6|Research and Development  |Research and Development            |2008-04-30 00:00:00.000|
           7|Production                |Manufacturing                       |2008-04-30 00:00:00.000|
           8|Production Control        |Manufacturing                       |2008-04-30 00:00:00.000|
           9|Human Resources           |Executive General and Administration|2008-04-30 00:00:00.000|
          10|Finance                   |Executive General and Administration|2008-04-30 00:00:00.000|
          11|Information Services      |Executive General and Administration|2008-04-30 00:00:00.000|
          12|Document Control          |Quality Assurance                   |2008-04-30 00:00:00.000|
          13|Quality Assurance         |Quality Assurance                   |2008-04-30 00:00:00.000|
          14|Facilities and Maintenance|Executive General and Administration|2008-04-30 00:00:00.000|
          15|Shipping and Receiving    |Inventory Management                |2008-04-30 00:00:00.000|
          16|Executive                 |Executive General and Administration|2008-04-30 00:00:00.000|

Click to view Full table

Sample Solution:

-- Selecting DepartmentID and Name columns from the Department table where there exists at least one record in a subquery
SELECT 
    -- Selecting the DepartmentID column from the Department table
    DepartmentID, 
    -- Selecting the Name column from the Department table
    Name   
-- Selecting data from the Department table
FROM 
    HumanResources.Department   
-- Filtering records where there exists at least one record in the subquery (SELECT NULL)
WHERE 
    EXISTS (SELECT NULL)  
-- Ordering the result set by the Name column in ascending order
ORDER BY 
    Name ASC ;

Explanation:

This SQL code retrieves DepartmentID and Name columns from the Department table.
The SELECT statement specifies the columns to be included in the result set.
The WHERE clause includes a subquery using EXISTS to check if there is at least one record in the subquery. The subquery SELECT NULL returns a single row with a NULL value. Since there is always at least one row with a NULL value, EXISTS returns true for all rows in the Department table, effectively not filtering any records.
The ORDER BY clause sorts the result set by the Name column in ascending order.
The result set will contain all departments along with their IDs, ordered alphabetically by name.

Sample Output:

departmentid|name                      |
------------+--------------------------+
          12|Document Control          |
           1|Engineering               |
          16|Executive                 |
          14|Facilities and Maintenance|
          10|Finance                   |
           9|Human Resources           |
          11|Information Services      |
           4|Marketing                 |
           7|Production                |
           8|Production Control        |
           5|Purchasing                |
          13|Quality Assurance         |
           6|Research and Development  |
           3|Sales                     |
          15|Shipping and Receiving    |
           2|Tool Design               |

Go to:

PREV : Retrieve rows whose datetime values are between '20111212' and '20120105'.
NEXT : Get employees with Johnson last names.

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.