17:16

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

Prepare for the second stage of testing

Typical problems

Once more about NULL values page 3

Comparison of the rows containing NULLs

It is well known that a predicate is evaluated as UNKNOWN when using the comparison with NULL, i.e. neither TRUE nor FALSE. So It is not surprising that when comparing NULLs with each other, these are considered equal in some cases, and not equal in other ones. Let's go to examples.

Begin with joining of two similar rows containing NULLs on equality of all the columns.

Console

Execute

WITH A AS (
SELECT 'a' a, NULL b
)
, B AS (
SELECT 'a' a, NULL b
)
SELECT * FROM A JOIN B ON A.a=B.a AND A.b=B.b;

Only those rows will be concatenated which the predicate is evaluated as TRUE for. In our example the predicate is UNKNOWN, so we do not get any rows.

Nevertheless the intersection of the queries (as well as uniting and exception) consider these rows identical.

Console

Execute

WITH A AS (
SELECT 'a' a, NULL b
)
, B AS (
SELECT 'a' a, NULL b
)
SELECT * FROM A 
INTERSECT
SELECT * FROM B;

a	b
a	NULL

It can be concluded that NULL-values are not considered equal (nor nonequal also) in horizontal operations, but equal in vertical operations. In particular, when grouping by column including NULLs, the latter ones form one group.

Finally let's consider a few solutions to the problem of determining the number of printers with unknown prices. The PrinterN table differs from the Printer table in that the couple of prices is set in NULL.

(1) Substraction of number of rows with known price from total number of rows.

Console

Execute

SELECT COUNT(*) - COUNT(price) qty FROM printerN;

(2) Using IS NULL predicate to calculate number of NULLs in price column.

Console

Execute

SELECT COUNT(*) FROM printerN WHERE price IS NULL;

(3) Grouping by price with taking the group formed by unknown price.

Console

Execute

SELECT COUNT(*) FROM printerN GROUP BY price HAVING price IS NULL;

Pages

HAVING NULL IS NULL

Previous [ORDER BY clause]

[Three-valued logic and WHERE clause] Next

Last added:

Tags

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags

The book was updated
month ago

продать казкоммерцбанк

SQL Problems and solutions

S. I. Moiseenko

Prepare for the second stage of testing

Typical problems

Once more about NULL values page 3

Comparison of the rows containing NULLs