Why NULL never compares false to anything in SQL

One of the most common questions SQL beginners have is why NULL values “don’t work right” in WHERE clauses. In this article I’ll explain it in a way I hope will make sense and be easy to remember.

If you’re new to SQL and have a hard time understanding this article, I encourage you to keep puzzling over it until the light comes on. I had to do that myself (and I’ve had to think hard about it to write this article), and I’ve seen a number of people learn SQL. NULLs always seem to be an important sticking point.

The query that won’t work right

Here are two common queries that just don’t work:

select * from table where column = null;

select * from table where column <> null;

They both return no rows! Countless SQL veterans have tried to explain this one to beginners. The beginner usually thinks the first row should return rows wherec1 is NULL. The veteran then points out that NULL is never equal to anything. The beginner then thinks, “if NULL isn’t equal to anything, then ‘WHERE COLUMN IS NOT EQUAL TO NULL’ is always true, so the second query should return all results!” The second WHERE clause is the logical opposite of the first, right? Right? Sadly, no it’s not.

The real problem: a language trap

The beginner has fallen into a language trap, which the experienced programmer probably set by saying “NULL is never equal to anything.” That statement seems to imply “NULL is NOT EQUAL TO.” Unfortunately, that’s wrong. Not only is NULL not equal to anything, it’s also not unequal to anything. This is where the language is confusing.

The truth is, saying anything with the words “equal” or “not equal” is a trap when discussing NULLs, because there is no concept of equality or inequality, greater than or less than with NULLs. Instead, one can only say “is” or “is not” (without the word “equal”) when discussing NULLs.

The right way to think about NULL

The correct way to understand NULL is that it is not a value. Not “this is aNULL value” but “this NULL is not a value.” Everything either is a value, or it isn’t. When something is a value, it is “1,” or “hello,” or “green,” or “$5.00″ etc – but when something isn’t a value, it just isn’t anything at all. SQL represents “this has no value” by the special non-value NULL. When someone says “theNULL value,” one should mentally disagree, because there’s no such thing.NULL is the complete, total absence of any value whatsoever.

What do you get when you compare a value to NULL?

Short answer: NULL. Every time. The result of comparing anything to NULL, even itself, is always, always NULL. A comparison to NULL is never true or false. Since NULL can never be equal to any value, it can never be unequal, either.

Sometimes people have difficulty understanding why a comparison to NULL can never be either true or false. Here’s an informal proof that may help:

Given the following predicates,

1. NULL is not a value
2. No value can ever be equal to a non-value

Here’s the proof by contradiction: Pretend for a moment that NULL is unequal to a value – say a real number, excluding infinity and negative infinity. I’ll choose an example number, say 5.

1. Assume that NULL <> 5.
2. That is, NULL <> 5 is a true expression (comparison operations are boolean, true or false).
3. That means “NULL < 5 or NULL > 5” is true, since I’m dealing with finite, real numbers; if it’s not equal, it must be bigger or smaller.
4. Therefore, there exists a real number equal to NULL; it’s either less than 5 or greater than 5.
5. That’s a contradiction, because I took it as a given that no value can be equal to NULL.

Therefore NULL is neither equal to a value nor unequal to it, so any comparison involving NULL is neither true nor false. The result of a comparison involvingNULL is not a boolean value – it is a non-value. You just can’t compare something that exists with something that doesn’t exist.

It has to be this way, because if a comparison to a non-value had a defined value, every query could be rewritten to return a wrong result. It would be possible to transform expressions to equivalent expressions that gave the opposite answer, and so on.

The correct way to write the queries

Instead of using boolean comparison operators such as less-than and greater-than, equal-to and not-equal-to, these queries must be written with the special comparison operator IS NULL:

select * from table where column is null;

select * from table where column is not null;

The IS NULL operator tests whether a value is null or not null, and returns a boolean.

The truth is, I lied

I’m trying to write this article to help people understand how non-values work in queries, so I’m being generous with the truth.

Since computers only work with things that exist, non-existence isn’t really possible, so NULLs must internally be implemented as some value, somewhere – even if it’s a value that indicates another value isn’t a value (huh?).

I’m glossing over something about comparisons to NULL, too. NULLs result in tri-valued logic; booleans are no longer just TRUE and FALSE, but can beUNKNOWN, too. The result of comparing NULLs is UNKNOWN, which is not the same thing as NULL, but that’s just semantic differences and deep mathematical pondering, and doesn’t materially affect how you write queries.

MySQL, for example, implements UNKNOWN as NULL, though it it isn’t perfectly consistent about it – try these queries:

select unknown;
select null;
select true;
select false;
select null is unknown;
select false is null;
select true is null;
select unknown is null;

Just remember NULL is neither equal nor unequal to anything, and I promise you will always be safe. It’s no use to get really picky about the fine points ofNULL versus UNKNOWN and all that.

A puzzler with COUNT

Someone posted a comment on the MySQL manual page about extensions to the GROUP BY clause, and I think it’s interesting to discuss here. The query is a way to count subsets within a group:

select shoeStyle,
   count(color) as Count,
   count(color = 'red' OR NULL) as redCount,
   count(color = 'green' OR NULL) as greenCount,
   count(color = 'blue' OR NULL) as blueCount
from bowlingShoes
group by shoeStyle;

The comment’s author said “OR NULL is necessary, or you will just get a count of all rows in the group.” Why is this?

If the OR NULL is omitted, the result of the expression is a boolean, TRUE orFALSE, which are actual values. The COUNT function counts any value that exists, not whether something is TRUE or FALSE, so the query is behaving correctly.

On the other hand, the result of the expression color = 'green' OR NULL is either TRUE or NULL. Boolean expressions are short-circuited when they’re evaluated. As soon as the first sub-expression in a logical OR expression is true, the whole result is true, so when the color is green, the expression is TRUEimmediately – a COUNT-able value. If the color isn’t green, the expression becomes FALSE OR NULL, which is NULL, of course – not a COUNT-able value.

You can see this in action with the following queries:

mysql> select true or null;
+--------------+
| true or null |
+--------------+
| 1            |
+--------------+
1 row in set (0.00 sec)

mysql> select false or null;
+---------------+
| false or null |
+---------------+
| NULL          |
+---------------+
1 row in set (0.00 sec)

What is the difference between Rollback, Commit and Savepoint is SQL?

All these statements fall in the category of Transaction Control Statements.

Rollback:

This is used for undoing the work done in the current transaction. This command also releases the locks if any hold by the current transaction. The command used in SQL for this is simply:

ROLLBACK;

Savepoint:

This is used for identifying a point in the transaction to which a programmer can later roll back. That is it is possible for the programmer to divide a big transaction into subsections each having a savepoint defined in it. The command used in SQL for this is simply:

SAVEPOINT savepointname;

For example:

UPDATE…..

DELETE….

SAVEPOINT e1;

INSERT….

UPDATE….

SAVEPOINT e2;

……

It is also possible to define savepoint and rollback together so that programmer can achieve rollback of part o a transaction. Say for instance in the above

ROLLBACK TO SAVEPOINT e2;

This results in the rollback of all statements after savepoint e2

Commit:

This is used to end the transaction and make the changes permanent. When commit is performed all save points are erased and transaction locks are released. In other words commit ends a transaction and marks the beginning of a new transaction. The command used in SQL for this is simply:

COMMIT;

Difference between UNION and UNION ALL clause – Oracle

UNION and UNION ALL used to combine ( set operation ) two or more query results.  UNION will eliminate duplicate rows and UNION ALL will display all rows.

SQL> select * from table_a;

No
–
1
2
2

SQL> select * from table_b;

No
—–
2
3

SQL> select * from table_a UNION select * from table_b;

No
—
1
2
3

SQL> select * from table_a UNION ALL select * from table_b;

No
—
1
2
2
2
3

Things to remember writing UNION queries

1. Number of columns in each UNION query must match

g :- select col1,col2 from table_a UNION select col1 from table_b; — This will not work;

 Instead you can replace column with null clause to match the number of columns

2. Data types must match

 Eg :- select ‘a’ from table_a UNION select 1 from table_b; — This will not work;

3. For large data set queries UNION might have performance issues. So use it very carefully.

Defining Program Incompatibility Rules

When a concurrent program is incompatible with another program, the two programs cannot access or update the same data simultaneously.
When you define a concurrent program, you can list those programs you want it to be incompatible with. You can also list the program as incompatible with itself, which means that two instances of the program cannot run simultaneously.

You can also make a program incompatible with all other concurrent programs by defining the program to be run-alone.

You define a concurrent program to be run-alone or to be incompatible with specific concurrent programs by editing the concurrent program’s definition using the Concurrent Programs window. See: Concurrent Programs.

Program incompatibility and run-alone program definitions are enforced using Conflict Domains.

Request Sets – Incompatibilities Allowed

When you define a request set or request set stage that allows incompatabilities, you create a concurrent program that runs the reports in your request set or stage according to the instructions you entered. Using the Concurrent Programs window, when you list programs as incompatible with a request set, those programs are prevented from starting until all the reports in the set or stage have completed running.
To define incompatibility rules for a request set and request set stage:

For a request set check the Allow Incompatibility check box on the Request Set window.
For a request set stage check the Allow Incompatibility check box on the Stages window.
Navigate to the Incompatible Programs block in the Concurrent Programs form and list those programs that your request set or stage is incompatible with.
All concurrent programs that run request sets are titled Request Set while all concurrent programs that run request set stages are titled Request Set Stage -Request Set . In the Concurrent Programs form, if you query a request set or stage concurrent program on the basis of the program’s name, you must enter in the Name field the words:
“Request Set” or “Request Set Stage” before the name of a concurrent program
“Request Set %” to perform a query on all request set and stage programs

Steps:

1.Login as System Administration responsibility.
2.Navigate to Concurrent > Set
3.Query on desired Request Set. For Example: Test_Report_set
4.Check the “Allow Incompatibility” check box in the Run Options and then save this record.
This step will automatically create a new concurrent program, the naming convention will be of the form “Request Set Test_Report_set ”
5.Navigate to Concurrent > Program > Define.
6.Query on new concurrent program “Request Set Test_Report_set “, remembering the concurrent program name begins with “Request Set…”.
7.Click on ‘Incompatibilities’ button located at the bottom of the form
8.In the Incompatible Programs form specify the name of the concurrent program,”Request Set Test_Report_set”, in the Name column and the value in the Scope column should be ‘ Set ‘.
9.Save this record.
10.Test the request set incompatibility

Incompatible Programs Window

Identify programs that should not run simultaneously with your concurrent program because they might interfere with its execution. You can specify your program as being incompatible with itself. See: Administer Concurrent Managers.

Application

Although the default for this field is the application of your concurrent program, you can enter any valid application name.

Name

The program name and application you specify must uniquely identify a concurrent program.

Your list displays the user-friendly name of the program, the short name, and the description of the program.

Scope

Enter Set or Program Only to specify whether your concurrent program is incompatible with this program and all its child requests (Set) or only with this program (Program Only).

Type –

Enter Domain or Global. If you choose Domain, the incompatibility is resolved at a domain-specific level. If you choose Global, then this concurrent program will be considered globally incompatible with your concurrent program, regardless of which domain it is running in.

Session Control Form

Field Description

i) Consumer Group – resource consumer group of the concurrent program can be specified. A resource consumer group defines a set of users who have similar resource usage requirements. An overall resource plan specifies how resources are distributed among the different resource consumer groups. Resource consumer groups and resource plans provide a method for specifying how to partition processing resources among different users.

ii) Rollback Segment – Rollback segment specified here would be used instead of the default rollback segment. If you specify a rollback segment here, your concurrent program must use the APIs FND_CONCURRENT.AF_COMMIT and FND_CONCURRENT.AF_ROLLBACK to use the specified rollback segment.

iii) Optimizer mode – Optionally specify an optimizer mode. You can choose ALL_ROWS, FIRST_ROWS, Rules, or Choose. You would specify an optimizer mode only for a custom program that may not perform well with the default cost-based optimizer (CBO) and needs tuning. You can use a different optimizer mode until your program is tuned for CBO.

Pivot Query in SQL

Introduction

This is a very simple example of Pivot query for the beginners. We use pivot queries when we need to transform data from row-level to columnar data.

Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance. It also help us to generate Multidimensional reporting.

Background

This post intends to help T-SQL developers get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past. 

Using the Code

Let us have a table name Invoice which has three properties, InvoiceNumber, InvoiceDate,InvoiceAmount. Suppose we have several rows input in the table. Our goal is to display the sum ofInvoiceAmount each month.

SELECT * FROM (SELECT year(invoiceDate) as [year], left(datename(month,invoicedate),3)as [month], _
InvoiceAmount as Amount FROM Invoice) as InvoiceResult 

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

Difference between CASE and DECODE in Oracle

Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional statement.

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

Example with DECODE function
Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function:

select
decode (
region,
‘N’,’North’,
‘S’,’South’,
‘E’,’East’,
‘W’,’West’,
‘UNKNOWN’
)
from
customer;
Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

Example with CASE statement

select
case 
region
when ‘N’ then ’North’
when ‘S’ then ’South’
when ‘E’ then ’East’,
when ‘W’ then ’West’
else ‘UNKNOWN’
end
from
customer;
Difference between DECODE and CASE:
Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Following is the list of differences -
1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.
2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.
3. CASE expects datatype consistency, DECODE does not.
4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
5. CASE executes faster in the optimizer than does DECODE.
6. CASE is a statement while DECODE is a function.

========================================================

DECODE and CASE statements in Oracle both provide a conditional construct, of this form:

if A = n1 then A1
else if A = n2 then A2
else X

Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.

1. CASE can work with logical operators other than ‘=’

DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc. It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.

An example of putting employees in grade brackets based on their salaries. This can be done elegantly with CASE.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> select ename   2       , case   3           when sal < 1000   4                then 'Grade I'   5           when (sal >=1000 and sal < 2000)   6                then 'Grade II'   7           when (sal >= 2000 and sal < 3000)   8                then 'Grade III'   9           else 'Grade IV'  10         end sal_grade  11  from emp  12  where rownum < 4;   ENAME      SAL_GRADE ---------- --------- SMITH      Grade I ALLEN      Grade II WARD       Grade II

2. CASE can work with predicates and searchable subqueries

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.

An example of categorizing employees based on reporting relationship, showing these two uses of CASE.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> select e.ename,   2         case   3           -- predicate with "in"   4           -- set the category based on ename list   5           when e.ename in ('KING','SMITH','WARD')   6                then 'Top Bosses'   7           -- searchable subquery   8           -- identify if this emp has a reportee   9           when exists (select 1 from emp emp1  10                        where emp1.mgr = e.empno)  11                then 'Managers'  12           else  13               'General Employees'  14         end emp_category  15  from emp e  16  where rownum < 5;   ENAME      EMP_CATEGORY ---------- ----------------- SMITH      Top Bosses ALLEN      General Employees WARD       Top Bosses JONES      Managers

3. CASE can work as a PL/SQL construct

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> declare   2    grade char(1);   3  begin   4    grade := 'b';   5    case grade   6      when 'a' then dbms_output.put_line('excellent');   7      when 'b' then dbms_output.put_line('very good');   8      when 'c' then dbms_output.put_line('good');   9      when 'd' then dbms_output.put_line('fair');  10      when 'f' then dbms_output.put_line('poor');  11      else dbms_output.put_line('no such grade');  12    end case;  13  end;  14  /   PL/SQL procedure successfully completed.

CASE can even work as a parameter to a procedure call, while DECODE cannot.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

SQL> var a varchar2(5); SQL> exec :a := 'THREE';   PL/SQL procedure successfully completed.   SQL> SQL> create or replace procedure proc_test (i number)   2  as   3  begin   4    dbms_output.put_line('output = '||i);   5  end;   6  /   Procedure created.   SQL> exec proc_test(decode(:a,'THREE',3,0)); BEGIN proc_test(decode(:a,'THREE',3,0)); END;                   * ERROR at line 1: ORA-06550: line 1, column 17: PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only ORA-06550: line 1, column 7: PL/SQL: Statement ignored     SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end); output = 3   PL/SQL procedure successfully completed.

4. Careful! CASE handles NULL differently

Check out the different results with DECODE vs NULL.

1 2 3 4 5 6 7 8 9

SQL> select decode(null   2              , null, 'NULL'   3                    , 'NOT NULL'   4               ) null_test   5  from dual;   NULL ---- NULL

1 2 3 4 5 6 7 8 9 10

SQL> select case null   2         when null   3         then 'NULL'   4         else 'NOT NULL'   5         end null_test   6  from dual;   NULL_TES -------- NOT NULL

The “searched CASE” works as does DECODE.

1 2 3 4 5 6 7 8 9 10 11

SQL>  select case   2         when null is null   3         then 'NULL'   4         else 'NOT NULL'   5         end null_test   6* from dual SQL> /   NULL_TES -------- NULL

5. CASE expects datatype consistency, DECODE does not

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

1 2 3 4 5 6 7 8

SQL> select decode(2,1,1,   2                 '2','2',   3                 '3') t   4  from dual;            T ----------          2

1 2 3 4 5 6 7 8 9

SQL> select case 2 when 1 then '1'   2              when '2' then '2'   3              else '3'   4         end   5  from dual;             when '2' then '2'                  * ERROR at line 2: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

6. CASE is ANSI SQL-compliant

CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

7. The difference in readability

In very simple situations, DECODE is shorter and easier to understand than CASE.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

SQL> -- An example where DECODE and CASE SQL> -- can work equally well, and SQL> -- DECODE is cleaner   SQL> select ename   2       , decode (deptno, 10, 'Accounting',   3                         20, 'Research',   4                         30, 'Sales',   5                             'Unknown') as department   6  from   emp   7  where rownum < 4;   ENAME      DEPARTMENT ---------- ---------- SMITH      Research ALLEN      Sales WARD       Sales   SQL> select ename   2       , case deptno   3           when 10 then 'Accounting'   4           when 20 then 'Research'   5           when 30 then 'Sales'   6           else         'Unknown'   7           end as department   8  from emp   9  where rownum < 4;   ENAME      DEPARTMENT ---------- ---------- SMITH      Research ALLEN      Sales WARD       Sales

Complicated logical comparisons in DECODE, even if technically achievable, are a recipe for messy, bug-prone code. When the same can be done more cleanly with CASE, go for CASE.