SQL Server 2016 AdventureWorks NULL checks

Question

I am working on 2 different assignments where I have to do null checks but I'm not sure if I have written the syntax correctly for that my instructor has not really discussed this but will be marking for it.

Below are the 2 questions and what I have written. Any help is appreciated.

Assignment 1 question: Create a list of the sales order numbers for orders not ordered online and not with a credit card. Note: 0 is false and 1 is true for bit fields. Below is the syntax i used, am i doing a null check here?

SELECT SalesOrderNumber
FROM Sales.SalesOrder_json
WHERE OnlineOrderFlag = 0 AND CreditCardID IS NULL

Assignment 2 question: list the vendors that have no products. Below is the syntax I used, am I doing a null check here?

SELECT 
    pv.Name AS Vendors, 
    COUNT(PP.ProductID) AS 'Products'
FROM 
    Purchasing.Vendor AS PV
LEFT JOIN 
    Purchasing.ProductVendor AS PPV ON PV.BusinessEntityID = PPV.BusinessEntityID
LEFT JOIN 
    Production.Product AS PP ON PP.ProductID = PPV.ProductID 
GROUP BY 
    PV.Name
HAVING 
    COUNT(PP.ProductID)  = 0; 

Answer 1

Welcome to Stack Overflow!
In the future, please post a summary or create table statements that represents the schema of the tables used in your queries so that we have enough information to provide more than speculative responses. Even though this is the Adventure Works DB, you should start your SO journey with good habits!

please try not to post direct Assignment questions online as you will easily get done for plagiarism by most academic assignment checkers, mainly because other students may see your post, and the support that you get from the community which could result in all of you handing in the same result.

Have you run your queries? Do you think the results are correct?

If the results from your queries are correct, then the only issue is "have you done any null checks"? One could say that if your results have returned the correct results then you must have satisfied the criteria, otherwise the question wasn't formulated very well.

Null checks can be summarised into 3 patterns:

1. You directly compare against null using IS NULL or IS NOT NULL in your query

2. Use of JOIN syntax to deal with data that may have nulls.

   • INNER JOIN will limit the results to only records that match in both tables. Use this if you need to omit records that have a null in the foreign key field.
   • Non INNER joins, like LEFT JOIN. This will return results from the left table, even if there are no matching records in the joined or right table.
     • This is a good discussion on all supported joins: LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

3. Use of Aggregation functions, aggregates will generally omit null values, COUNT will return 0 if all values are NULL, where as other aggregates such as SUM, MIN, MAX, AVG will return NULL if all values are NULL

Question 1
Clearly you have implemented a NULL check because you have evaluated criteria directly on the nullable column.

It looks like your answer to Question 1 is pretty good.

Question 2 While your query looks like it would return the vendors with no products, it is also returning a count of zero.

You do not need to output a column so that you can use it in a filter criteria, so remove COUNT(PP.ProductID) AS 'Products' unless you have been otherwise instructed to use it.

Is this a NULL check... That up to the interpretation, I think in this case the answer is yes. By using LEFT JOIN (or OUTER joins) you have created a result set that will have the field PP.ProductID with a value of NULL If there are no products.

Using Count in the filter criteria over that null column and recognising that a Count with a zero result means that the ProductID column was in fact null means you have evaluated a null check.

There are other ways to query for the same results, such as using NOT EXISTS. NOT EXISTS would NOT be a direct null check, because NULLABILITY was not evaluated directly.