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’.

sql-demo_query

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:

sql-demo_query2

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:

image

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:

SQL Queries

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


6 comments:

Suprotim Agarwal said...

Simple. Informative. Good read! I liked the way you took the IPL example to explain this concept.

Ankit Kashyap said...

Thanks Suprotim!

Unknown said...

Nice perspective! I never knew SQL check constraint rejects False!

Anonymous said...

Very clever isn't it! I like the way you can compose the query and submit it to the database which returns the results.

Who would have thought of this concept. I think it can be applied to large scale storage of data on the hard disk and provide easy way for people to make the query without the wearing the white coat LED blinkers.

JP @ mysql command tutorial said...

Great one mate. This is one of fundamental thing i learn : use IS NULL for checking NULL in SQL but your example is just too good and supports the concept.

Javin
grep command in unix with example

Anonymous said...

specifying "SET ANSI_NULLS ON" is supposed to fix this issue during comparisons:
http://msdn.microsoft.com/en-us/library/ms188048.aspx