Database foundations
NULL and three-valued logic
Unknown, an empty string and zero are different.
Understand the concept
NULL represents missing information, not zero or an empty string. Comparisons can produce UNKNOWN in addition to TRUE and FALSE. WHERE keeps only TRUE rows. Email = NULL therefore does not find missing emails; use IS NULL.
Arithmetic involving NULL usually produces NULL. COALESCE selects the first non-NULL value, but replacing missing information is a business decision. Treating an unknown price as zero can create a misleading sales total. COUNT(*) counts every row, whereas COUNT(Email) counts non-NULL emails. Most aggregates ignore NULL inputs.
A NULL returned by a NOT IN subquery is a serious trap: UNKNOWN comparisons may remove the rows you expected. Correlated NOT EXISTS often expresses absence more clearly. Always consider both SQL’s NULL rules and the meaning of missing information: no recorded value and no event are not the same thing.
Use it when filtering, counting or filling missing fields, or checking for absent relationships.
Using = NULL or forgetting NULL in a NOT IN result.
One topic. Three levels of depth.
SELECT CustomerId, FullName FROM lab.Customer WHERE Email IS NULL ORDER BY CustomerId;Can and Mert are returned. An empty string would not satisfy this condition.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Display a meaningful replacement for a missing email without changing the record.
Show the solution and explanation
SELECT FullName, COALESCE(Email, N'Not provided') AS DisplayEmail FROM lab.Customer;COALESCE changes the displayed result, not the stored data.