Generally speaking from a user standpoint, you don't want a Boolean expression to evaluate to NULL.
Writing SQL typically involves writing queries to explicitly avoid NULL values in Boolean expressions. IMX, developers would consider using three valued logic intentionally would be considered an abuse of three valued logic. A properly written query should handle NULLs and understand them. You don't write them in such a way that they happen to work right when something is NULL. Usually this involves COALESCE()
or IS NULL
or IS NOT NULL
somewhere.
It is, however, vital that you understand the logic, because NULLs exist and are unavoidable for most real-world data.
For example, let's say I'm working on a table of students. The table has First, Middle, and Last name fields. I want to know the list of students that don't have a middle name. Now, some applications will store an empty string, ''
, and some applications will store a NULL value, and some applications might do both (and some RDBMSs like Oracle treat empty strings as NULLs). If you were unsure, you could write it as:
SELECT *
FROM Student
WHERE MiddleName = ''
OR MiddleName IS NULL;
The other common scenario is when you're OUTER JOINing to another table. Let's say you're comparing the paychecks for teachers. You have a table for Checks, and a table for CheckDetail. You want to know how much teachers pay for Benefits. Your report needs to list all teachers, even if they're contractors who don't pay for benefits because they don't get any:
SELECT Check.Employee_Id,
SUM(CheckDetail.Amount) AS BenefitsDeductions
FROM Check
LEFT JOIN CheckDetail
ON Check.Id = CheckDetail.CheckId
AND CheckDetail.LineItemType = 'Benefits'
GROUP BY Check.Employee_Id;
You run your report, and you notice that your contractor teachers show NULL for BenefitsDeductions. Oops. You need to make sure that shows up as a zero:
SELECT Check.Employee_Id,
COALESCE(SUM(CheckDetail.Amount),0) AS BenefitsDeductions
FROM Check
LEFT JOIN CheckDetail
ON Check.Id = CheckDetail.CheckId
AND CheckDetail.LineItemType = 'Benefits'
GROUP BY Check.Employee_Id;
So you try that, and it works. No NULL values! But... a few days later, your users report that teachers who used to be contractors are showing up with 0s even though they're paying for benefits now. You've got to COALESCE before the SUM to keep those amounts:
SELECT Check.Employee_Id,
SUM(COALESCE(CheckDetail.Amount,0)) AS BenefitsDeductions
FROM Check
LEFT JOIN CheckDetail
ON Check.Id = CheckDetail.CheckId
AND CheckDetail.LineItemType = 'Benefits'
GROUP BY Check.Employee_Id;
Finding these kinds of corner cases and exceptions is what writing SQL is all about.
IF 1=2 AND NULL =1 PRINT 'TRUE' ELSE PRINT 'FALSE'
– Martin Smithif 1 = 1 and @a + 1 > 5
evaluates tounknown
nottrue
which is why you go to the second branch. Anif
only has two branches. To really evaluate the value you can useSELECT CASE WHEN your_condition THEN 'true' WHEN NOT your_condition THEN 'false' ELSE 'unknown' END
- so substituting in your latest exampleDeclare @a int = NULL; SELECT CASE WHEN 1 = 1 and @a + 1 > 5 THEN 'true' WHEN NOT 1 = 1 and @a + 1 > 5 THEN 'false' ELSE 'unknown' END
– Martin Smith