CODEWITHSUNDEEP
phpmysql
Bluemagica
Bluemagica

Reputation: 5158

How do I do this ternary operation in mysql?

....
.....
if ($prod_price->RecordCount()>0) {
              $p_price = $prod_price->fields['options_values_price'];
          }
          else
          {
              $p_price = $orders_products_query->fields['products_price'];
          }
          $p_price = ($p_price>0)?$p_price:$orders_products_query->fields['final_price'];
......
..........

As you can guess from the above, I am running two different queries and checking 3 fields to determine the variable $p_price in PHP. I want to condense this in a single query, the conditions being: If field1 is Null, use field2, if field2 is 0, use field 3.

The first part can be solved with IFNULL(field1,field2)....but what do I do about the second part? Should I use case? I need the most efficient solution in terms of execution speed, cause this is part of an extremely large query.

EDIT:

Since it seems the question isn't clear to some of you, take this as an alternative.

IF(IFNULL(field1,field2)>0,IFNULL(field1,field2),field3) The above MySQL query condition works with the above mentioned logic, but as you can see, it evaluates field1 and field2 twice for NULL checking, which I don't believe is very efficient, so I am looking for a better query/condition to rewrite the same thing.

Upvotes: 4

Views: 2049

Answers (4)

jcsanyi
jcsanyi

Reputation: 8174

You can use IF(condition, val-if-true, val-if-false) in MySQL.

You can also nest the IF() functions, something like this:

IF(field1 IS NOT NULL, field1, IF(field2 <> 0, field2, field3))

This corresponds to:

  • If field1 is NOT NULL, use field1, otherwise...
  • If field2 is non-zero, use field2, otherwise...
  • Use field3

Upvotes: 7

Curt
Curt

Reputation: 5722

The standard SQL COALESCE function looks like what you need:

COALESCE(a, b, c, d, "banana") means that the expression will take on the first non-null value it encounters, working from left to right. It is quite efficient, taking only a few processor cycles.

It can take on as many parameters as you need, from two, to a gazillion.

Upvotes: 2

liyakat
liyakat

Reputation: 11853

you can use like below

lets Try this :

select if(Id is null, 0, id) as Id;

And also mysql providing very good document to you conditional statement

Also if one more cases you can use like 

SELECT USER_ID, (CASE USER_ID WHEN 1 THEN 1 ELSE 0 END) as FIRST_USER FROM USER

let me know if i can help you further.

Upvotes: 1

Rohan Kumar
Rohan Kumar

Reputation: 40639

You can use ISNULL() or IS NOT NULL() in Mysql.

Upvotes: 1

Related Questions