The logic used by SQL is complicated because it uses three valued logic instead of two. Two valued logic is based on boolean algebra which return two value namely truth and fale. Boolean algebra can be operated using three basic operator namely AND, OR and NOT. If a unknown NULL value is used in boolean logic it will lead to violation of boolean algebra logic so a separate three valued logic was designed for SQL.
- Read: More Interview Questions
What is Three valued logic?
In boolean logic, the comparison between two object will result in either True or False. In three valued logic, the end result will give three logics namely: True, False and Unknown. The operation used in three valued logic is similar to the boolean logic. The truth table for various three valued logic operation are given below.
AND Operation
| AND | TRUE | FALSE | UNKNOWN |
| TRUE | TRUE | FALSE | UNKNOWN |
| FALSE | FALSE | FALSE | UNKNOWN |
| UNKNOWN | UNKNOWN | UNKNOWN | UNKNOWN |
OR Operation
| OR | TRUE | TRUE | UNKNOWN |
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | UNKNOWN |
| UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
NOT Operation
| NOT | |
| TRUE | FALSE |
| FALSE | TRUE |
| UNKNOWN | UNKNOWN |
Example for Three Valued Logic
Consider that we have an table which contain columns with various smartphone brand and model number.
smartphone1
{
model1Number CHAR(32) NOT NULL,
Phone1Model CHAR(16),
}
The column’ will contain entries of several smartphone brands and model numbers. Now let’s assume that a developer thinks that the table contains details of both Tablets and smartphones and wants to display to display the details of tablets. You may think that writing the following code is enough to display the SQL table.
SELECT * FROM martphone1 WHERE Phone1Mode=null;
The code written above won’t return any value because of the three valued logic used in SQL. Understanding the logic of ternary logic i very important to write an effective SQL code. The problem in the above code i the equality operator. The equality operator tests the NULL column value. In all databases a comparison made with NULL will always result in an UNKNOWN value. Even if you compare a NULL value to NULL it will return an UNKNOWN value. The only way to check a NULL value i to use I NULL or IS NOT NULL.
- Recommended: Final year Academic projects download
The correct syntax to display Null value in SQL is,
SELECT * FROM martphone1 WHERE Phone1Model IS NULL
To use three valued logic in SQL, you have to unlearn the Boolean algebra concepts