SQL Queries – beyond TRUE and FALSE

Most of the SQL novices are more accustomed to thinking in terms of two-valued logic (TRUE, FALSE) in SQL. But SQL uses three-valued logic – TRUE, FALSE and UNKNOWN. It means that the value of an expression may be TRUE, FALSE or UNKNOWN. Confused?

I guess nothing much needs to be explained about TRUE & FALSE. Let’s focus on UNKNOWN. In one line: SQL uses NULL for missing values and when the logical expression involves a missing value, it evaluates to UNKNOWN. Simple!! So what is so important about it?

Accept TRUE and Reject FALSE

In a two-valued logic system, Accept TRUE  is same as Reject FALSE. But in SQL, both are different.

Accepts TRUE = Reject both FALSE and UNKNOWN

Rejects FALSE = Accepts both TRUE and UNKNOWN

SQL query filters (e.g. Where) “Accept TRUE”. That means if the expression is “True”, only then will it be included, otherwise dropped from the result collection.

Let’s demonstrate this behavior with an example. We will create a table which will have 2 columns – Player and Team. Then we will filter out the players who play for team ‘MI’.

Here’s the query for you to try out

--Create a database and table for Demo
CREATE DATABASE Experiment
GO
USE Experiment
GO
create Table Player_Team (
Player nvarchar(100) not null,
Team nvarchar(100) null)
Go
--Insert some values
Insert into Player_Team Values ('Sachin','MI')
Insert into Player_Team Values ('Dhoni','CSK')
Insert into Player_Team Values ('Sehwag','DD')
Insert into Player_Team Values ('Bhajji','MI')
Insert into Player_Team Values ('Zak','MI')
Insert into Player_Team Values ('Kaif',NULL)
Insert into Player_Team Values ('Arun Lal',NULL)

--Query to fetch player name who play for MI
Select * from Player_Team where (Team='MI')

The output of above Select query will be:

Only three players have Team Values as MI. And condition (Team='MI') is true for them. For ‘Kaif’ and ‘Arun Lal’, the team value is NULL, which will be evaluated as UNKNOWN. As said earlier, SQL query filter accepts true (= reject False and UNKNOWN) hence only 3 rows.

Now let’s try to fetch players, who don’t play for MI.

--Query to fetch who don't play for MI
Select * from Player_Team where (Team<>'MI')

Here is the output:

In our table, out of 7, 3 players play for MI so this query should fetch the other 4 players. But it returns only 2 rows, because again for ‘Kaif’ and ‘Arun Lal’ (team value is NULL), expression Team<>’MI’ will be evaluated as UNKNOWN - and again they will be rejected.

To correct this query we need to use ‘is null’ predicate as shown in the below query:

--Query to fetch who don't play for MI -- Correct Approach
Select * from Player_Team where (Team<>'MI') or Team is null

And here is the correct expected output:

The counterpart of “Is Null” predicate is “Is not null”. So from next time, whenever you have some NULL values in your table, make sure to include proper predicates in queries to handle them.

So we learnt that SQL query filters accepts TRUE but that's not all. Surprisingly SQL check constraints rejects FALSE (accept TRUE and UNKNOWN). Read here an excellent post explaining it.

Last but not the least, here is another twist for you. An expression comparing two NULLs (NULL = NULL) evaluates to UNKNOWN. But for grouping and sorting purposes, two NULLs are considered equal.

Conclusion

This article demonstrates the 3 valued-logic in SQL and how to write queries to accommodate its effect. Hope you guys enjoyed this article!

Ankit primarily works on SQL Server reporting services and sometimes even on ASP.NET AJAX and jQuery. An ardent C programmer, Bollywood movie buff and self-proclaimed singer, Ankit love to listen, read and even speak about various Microsoft technologies. Follow him on twitter @ankitwww