Re: Clarification on NULL Records
From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 05/10/04
- Next message: Nic: "Regional Settings"
- Previous message: Prabhat: "SQL Server Internals"
- In reply to: Ana: "Clarification on NULL Records"
- Next in thread: Aaron Bertrand [MVP]: "Re: Clarification on NULL Records"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 10 May 2004 07:26:48 +0100
NULL represents a missing/unknown attribute rather than an actual value. The
basic comparison operators like =, <>, <, >, IN, etc return an UNKNOWN
result when NULLs are involved and comparisons that return UNKNOWN are
excluded by the WHERE clause.
Read about NULLs and three-value logic in Books Online:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_02_8pwy.asp
Use the IS NULL / IS NOT NULL predicate to test for the presence or absence
of NULLs:
SELECT *
FROM table1
WHERE required <> 'Y'
OR required IS NULL
It isn't good practice to use NULL to represent a known value of an
attribute because it makes your code more complex and may prevent the
optimizer from using an index to speed up your queries. For example if your
"Required" column was intended to represent the values of "Y" and "N" then
better declare it like this:
CREATE TABLE dbo.Table1 (names NVARCHAR (50),required CHAR(1) NOT NULL CHECK
(required IN ('Y','N')) DEFAULT 'N')
Notice the use of a DEFAULT to avoid NULLs. Now you can write:
SELECT *
FROM table1
WHERE required = 'N'
-- David Portas SQL Server MVP --
- Next message: Nic: "Regional Settings"
- Previous message: Prabhat: "SQL Server Internals"
- In reply to: Ana: "Clarification on NULL Records"
- Next in thread: Aaron Bertrand [MVP]: "Re: Clarification on NULL Records"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
