CODEWITHSUNDEEP
sql
Tiago Castro
Tiago Castro

Reputation: 421

How can I make IF without ELSE on SQL WHERE condition?

I`m trying to make a querie that selects users and if user type equals 1 I need to select those with age. My table:

  id (int 11) | type (int 11) | email (varchar 25) | age (int 11)

My querie:

SELECT * FROM users WHERE IF(type = 1, age <> 0)

The problem is that I need to have an ELSE condition, but I dont need one in this case. How can I make an IF inside WHERE without else condition?

Thanks

Upvotes: 2

Views: 4098

Answers (4)

user31782
user31782

Reputation: 7589

I was in a similar situation and ended up with the following solution:

SELECT * FROM users WHERE IF(type = 1, age <> 0, 1=0)

The else part here is 1 = 0 which is never true, so you don't select anything in that case.

Upvotes: -1

spencer7593
spencer7593

Reputation: 108400

Q: How do I make IF without ELSE on SQL WHERE condition ?

A: It's not possible; there is always an ELSE. MySQL IF() function has three arguments. It doesn't matter where the IF() function is used, whether it's part of an expression in a WHERE clause, or an expression in the SELECT list.

As an alternative to the MySQL IF() function, we can use a more portable, more ANSI-standard compliant CASE expression. But that doesn't get away from the crux of the question, about avoiding an ELSE. There is always an ELSE with the CASE expression as well. If we omit the ELSE clause, it's the same as if we had specified ELSE NULL.

As an aside (unrelated to the question that was asked), I don't think we should be storing age as an attribute; typically age is the difference between the current date and a date in the past (date of birth, registration date, etc.)

I'm thinking we don't need an IF function in the WHERE clause. (That's specific to MySQL, so this answer assumes that the target DBMS is MySQL, and not some other RDBMS).

We can use a combination of conditions, combined with NOT, AND, OR and parens so specify an order of operations.

Sample data and example output goes a long way to explaining the spec.

 id  type  age    email
 --  ----  ----   ----------
  1     0     0   1@one
  2     1     0   2@two
  3     0     1   3@three
  4     1     1   4@four
  5     0  NULL   5@five
  6     1  NULL   6@six
  7  NULL  NULL   7@seven
  8  NULL     0   8@eight
  9  NULL     1   9@nine

Which of these rows should be returned, and which rows should be excluded?

Here is an example query (MySQL specific syntax) that returns all rows except row id=2 (type=1, age=0)

 SELECT u.id
      , u.type
      , u.age
      , u.email
   FROM user u
  WHERE NOT ( u.type <=> 1 ) 
     OR NOT ( u.age  <=> 0 )

If there's a requirement to incorporate IF functions, we can do that, and return an equivalent result:

 SELECT u.id
      , u.type
      , u.age
      , u.email
   FROM user u
  WHERE NOT ( IF( u.type <=> 1 ,1,0) )
     OR NOT ( IF( u.age  <=> 0 ,1,0) )
              ^^^              ^^^^^

In the WHERE clause, an expression will be evaluated as a boolean value. A numeric value of 0 is FALSE, a non-zero value is TRUE, and NULL value is (as always) just NULL.

For a row to be returned, we need the expression in the WHERE clause to evaluate to a non-zero value (to evaluate to TRUE).

The third argument of the IF() function is the "else" value; for that value, we can return TRUE, FALSE or NULL. To exclude rows that do not satisfy the type=1 condition, we return either zero or NULL:

  WHERE IF(type = 1, age <> 0 ,0 )
                              ^^

or equivalently:

  WHERE IF(type = 1, age <> 0 ,NULL )
                              ^^^^^

If we want rows that don't satisfy type=1 condition to be returned, we can return any non-zero value: 

  WHERE IF(type = 1, age <> 0 ,42 )
                              ^^^

RECAP:

Addressing the question that was asked:

Q: How do I make IF without ELSE on SQL WHERE condition ?

A: There is always an ELSE value with the MySQL IF() function; in the context of the WHERE clause, the value will be evaluated as a boolean: TRUE, FALSE or NULL.

Upvotes: 1

forpas
forpas

Reputation: 164089

You can do it with CASE:

SELECT * FROM users 
WHERE age = CASE WHEN type <> 1 THEN age ELSE 0 END

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think you want:

SELECT *
FROM users
WHERE type <> 1 OR age <> 0;

Upvotes: 0

Related Questions