(9) T-SQL Fundamentals: NULL Marks

What order do NULL marks appear in TSQL in a sort?

*Before* the present values

What do NULL marks mean

A missing or unknown value.

Do Aggregates consider Nulls?

Count, Sum, AVG, MIN, MAX, LIST do not handle nulls. Count (asterisk) is the only aggregate that does. Counts all rows, even if the row has all nulls.

What accepts True only and excludes False and Unknown? "Accept True" What accepts True and Unknown but rejects False? "Reject False"

Filters accept True, and filter out False and Unknown.WHERE, HAVING and ON. CHECK constraints will allow Null.

Where are two null marks the same outside of predicates like WHERE, HAVING, and ON?

GROUP BY arranges null marks as one group. Nulls are equal. ORDER BY sorts null marks together. Nulls are equal.

What happens when you use IS NULL in the predicate? When you use IS NOT NULL? What is the correct syntax for IS NULL and IS NOT NULL?

If the value of the expression is null, it returns true. If the value of the expression is null, it returns false. expression IS [ NOT ] NULL

Are Null values equal to each other in a Predicate? Null=Null? Is x=2 True, False, or Unknown? Is x=y True False or Unknown?

No. Null represents a missing value, an unknown. Unknown, we don't know what x is. Unknown, we don't know what x or y is.

What is good practice?

SQL is inconsistent with how it treats Null marks so you should explicitly thing of NULL marks and three value logic in every query you write.

How would you return all rows for which a region is NULL? What would happen if you used Where region = null

Select custid, country, region, city From Sales.Customers Where region IS NULL; This would evaluate to unknown for every row and no records would be returned. Null does not equal null.

How would you return all rows for which the region is not WA including null?

Select custid, country, region, city From Sales.Customers Where region is <> N'WA' OR region IS NULL;

How does Standard SQL treat Null marks for the Unique constraint? How does SQL Server?

Standard SQL allows multiple nulls in the unique column. Two nulls are not equal. SQL Server allows *one* null to appear in the unique column. Nulls are considered equal for a Unique constraint.

What will this query do? Select custid, country, region, city From Sales.Customers Where NOT (region = N'WA';)

This is the same as using the <> operator. You will get all rows that have any value in them except null and WA.

What will this query do? Select custid, country, region, city From Sales.Customers Where region = N'WA';

This will return all rows with custid, country, region, and city where the value in region evaluates to TRUE (value is WA). You will see no nulls or any other value but WA in region

What will this query do? Select custid, country, region, city From Sales.Customers Where region <> N'WA';

This will return all rows with custid, country, region, and city where the value in region evaluates to TRUE (value is not WA). You will see no nulls and no WA in region.

How do you find Null values given that one null is not equal to another null?

Use the predicate *IS NULL* or it's inverse of *IS NOT NULL*