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

Last update on December 20 2024 11:37:36 (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.

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               |

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

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