AdventureWorks Database: Check for similarity of the values

SQL Query - AdventureWorks: Exercise-131 with Solution

131. From the following table, write a query in SQL to check the values of MakeFlag and FinishedGoodsFlag columns and return whether they are the same or not. Return NULL if MakeFlag is equal to FinishedGoodsFlag, otherwise return the value of MakeFlag. Return the columns ProductID, MakeFlag, FinishedGoodsFlag, and a column indicating NULL if they are equal. Filter the results for products where ProductID is less than 10.

productid|name                            |productnumber|makeflag|finishedgoodsflag|color       |safetystocklevel|reorderpoint|standardcost|listprice|size|sizeunitmeasurecode|weightunitmeasurecode|weight |daystomanufacture|productline|class|style|productsubcategoryid|productmodelid|sellstartdate          |sellenddate            |discontinueddate|rowguid                             |modifieddate           |
---------+--------------------------------+-------------+--------+-----------------+------------+----------------+------------+------------+---------+----+-------------------+---------------------+-------+-----------------+-----------+-----+-----+--------------------+--------------+-----------------------+-----------------------+----------------+------------------------------------+-----------------------+
        1|Adjustable Race                 |AR-5381      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |694215b7-08f7-4c0d-acb1-d734ba44c0c8|2014-02-08 10:01:36.827|
        2|Bearing Ball                    |BA-8327      |false   |false            |            |            1000|         750|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |58ae3c20-4f3a-4749-a7d4-d568806cc537|2014-02-08 10:01:36.827|
        3|BB Ball Bearing                 |BE-2349      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |9c21aed2-5bfa-4f18-bcb8-f11638dc2e4e|2014-02-08 10:01:36.827|
        4|Headset Ball Bearings           |BE-2908      |false   |false            |            |             800|         600|           0|        0|    |                   |                     |       |                0|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |ecfed6cb-51ff-49b5-b06c-7d8ac834db8b|2014-02-08 10:01:36.827|
      316|Blade                           |BL-2036      |true    |false            |            |             800|         600|           0|        0|    |                   |                     |       |                1|           |     |     |                    |              |2008-04-30 00:00:00.000|                       |                |e73e9750-603b-4131-89f5-3dd15ed5ff80|2014-02-08 10:01:36.827|
      317|LL Crankarm                     |CA-5965      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |L    |     |                    |              |2008-04-30 00:00:00.000|                       |                |3c9d10b7-a6b2-4774-9963-c19dcee72fea|2014-02-08 10:01:36.827|
      318|ML Crankarm                     |CA-6738      |false   |false            |Black       |             500|         375|           0|        0|    |                   |                     |       |                0|           |M    |     |                    |              |2008-04-30 00:00:00.000|                       |                |eabb9a92-fa07-4eab-8955-f0517b4a4ca7|2014-02-08 10:01:36.827|
	  -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the Product table and using the NULLIF function to compare MakeFlag and FinishedGoodsFlag
SELECT 
    -- Selecting the ProductID column
    ProductID, 
    -- Selecting the MakeFlag column
    MakeFlag, 
    -- Selecting the FinishedGoodsFlag column
    FinishedGoodsFlag,   
    -- Applying the NULLIF function to return NULL if MakeFlag is equal to FinishedGoodsFlag
    NULLIF(MakeFlag, FinishedGoodsFlag) AS "Null if Equal"
-- Selecting data from the Product table
FROM 
    Production.Product  
-- Filtering records where ProductID is less than 10
WHERE 
    ProductID < 10;

Explanation:

• This SQL code selects specific columns from the Product table and uses the NULLIF function to compare MakeFlag and FinishedGoodsFlag.
• The NULLIF function returns NULL if the two specified expressions are equal; otherwise, it returns the first expression.
• In this case, NULLIF(MakeFlag, FinishedGoodsFlag) will return NULL if MakeFlag is equal to FinishedGoodsFlag.
• The result set includes columns for ProductID, MakeFlag, FinishedGoodsFlag, and the result of the NULLIF function, labeled as "Null if Equal".
• Records are filtered to include only those where ProductID is less than 10.
• This SQL query can be useful for identifying instances where MakeFlag and FinishedGoodsFlag are equal, potentially indicating inconsistencies or redundant data.

Sample Output:

productid|makeflag|finishedgoodsflag|Null if Equal|
---------+--------+-----------------+-------------+
        1|false   |false            |             |
        2|false   |false            |             |
        3|true    |false            |true         |
        4|false   |false            |             |

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Select data from first column that has a nonnull value.
Next: Return any distinct values that are returned by both the queries.