Joe Celko's SQL for Smarties, 3rd Edition

15.6. EXISTS and Three-Valued Logic

This example is due to an article by Lee Fesperman at FirstSQL. Using Chris Date’s “SupplierParts” table with three rows:

CREATE TABLE SupplierPart
(sup_nbr CHAR(2) NOT NULL PRIMARY KEY,
 part_nbr CHAR(2) NOT NULL,
 qty INTEGER CHECK (qty > 0));

sup_nbr  part_nbr  qty
======================
'S1'     'P1'   NULL
'S2'     'P1'   200
'S3'     'P1'   1000

The row (‘S1’, ‘P1’, NULL) means that supplier ‘S1’ supplies part ‘P1’ but we do not know what quantity he has.

The query we wish to answer is “Find suppliers of part ‘P1’, but not in a quantity of 1000 on hand.” The correct answer is ‘S2’. All suppliers in the table supply ‘P1’, but we do know ‘S3’ supplies the part in quantity 1000 and we do not know in what quantity ‘S1’ supplies ...