[Solved] How can you use NULLIF in the where clause?

EverSQL Database Performance Knowledge Base

How can you use NULLIF in the where clause?

I am trying to do something like this:

select
     col_1, col_2, etc
from
     table
where
     col_1 = nullif('', '')

Am I doing this incorrectly? I am not getting any results back.

Edit:

My expected results are to get every record back where col_1 is NULL.

I know I can use where col_1 is null, but I am using SSIS and a variable. Sometimes the col_1 is actually NULL and sometimes it is not.

Sample data:

 collaboration     first_name     last_name          city     
          NULL            Bob         Smith       Chicago
Data Migration           John         Smith        Austin
          NULL           Pika           Chu       Houston
    Production            ash       ketchum         tokyo

Sometimes I may want to return the records where collaboration is NULL, sometimes I want to return the records where it says Production.

I'd like to use the same query, if possible, with little modification.

Edit Part 2:

I tried to experiment with this.

select
     col_1, col_2, etc
from
     table
where
     case
         when col_1  = '' then NULL
         else col_1
         end

But I am getting the error message:

An expression of non-boolean type specified in a context where a condition is expected, near ORDER.

Query speed it not something I am concerned with.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `table` ADD INDEX `table_idx_col_1` (`col_1`);
The optimized query:
SELECT
        table.col_1,
        table.col_2,
        table.etc 
    FROM
        table 
    WHERE
        table.col_1 = nullif('', '')

Related Articles



* original question posted on StackOverflow here.