AdventureWorks Database: Look for rows where middlename is NULL

SQL Query - AdventureWorks: Exercise-175 with Solution

175. From the following table write a query in SQL to identify the rows with a middlename that is not NULL. Return businessentityid, persontype, firstname, middlename,and lastname. Sort the result set in ascending order on firstname.

Sample table: Person.Person

businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- This SQL query selects specific columns from the person.person table, applying a condition to filter the results based on values in the MiddleName column.
-- Selecting columns: BusinessEntityID, PersonType, FirstName, MiddleName, and LastName.
SELECT businessentityid, persontype, firstname, middlename, lastname
-- From the person.person table.
FROM person.person
-- Filtering the results where the MiddleName column is not distinct from NULL.
WHERE middlename IS NOT DISTINCT FROM NULL
-- Ordering the results by the FirstName column in ascending order.
ORDER BY firstname;

Explanation:

• The query selects several columns from the person.person table: BusinessEntityID, PersonType, FirstName, MiddleName, and LastName.
• It filters the results to include only rows where the MiddleName column is not distinct from NULL.
• The IS NOT DISTINCT FROM operator is used to check for equality while treating NULL values as indistinct.
• Finally, the results are ordered by the FirstName column in ascending order.
• This query retrieves records from the person table where the MiddleName is either NULL or not NULL, considering them as equal, and orders the results alphabetically by FirstName.

Sample Output:

businessentityid|persontype|firstname               |middlename|lastname              |
----------------+----------+------------------------+----------+----------------------+
             222|EM        |A. Scott                |          |Wright                |
           15964|IN        |Aaron                   |          |Roberts               |
           15693|IN        |Aaron                   |          |Edwards               |
            2272|GC        |Aaron                   |          |Con                   |
           17060|IN        |Aaron                   |          |Hernandez             |
             727|SC        |Aaron                   |          |Con                   |
           16664|IN        |Aaron                   |          |Hill                  |
            2306|GC        |Aaron                   |          |Nicholls              |
            5487|IN        |Aaron                   |          |Henderson             |
            5488|IN        |Aaron                   |          |Coleman               |
            5489|IN        |Aaron                   |          |Jenkins               |
            5491|IN        |Aaron                   |          |Perry                 |
            5492|IN        |Aaron                   |          |Powell                |
...	

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find the rows where middlename differs from NULL.
Next: Fetch all products with a weight of less than 10 pounds.