Logical Operator OR

ElArZ

Hey,

I'm having troubles figuring this out:
The functioning of OR in mysql is that it executes both conditions (before and after OR) and returns the result of both.

What I'm looking for is a way to be able to have the functionality of the php OR, meaning that only if the first condition is false, the second condition will be executed. If the first condition is true then the second one will not be executed.

Is there a way to achieve this?

Thank You.

Piranha

I don't understand what you mean. Let's assume we have the following table with data:

[b]id  firstname   lastname[/b]
1   Anders      Andersson
2   Anders      Larsson
3   Lars        Andersson
4   Lars        Larsson

And you run the following SQL:

select * from table where firstname = 'Anders' or lastname = 'Andersson';

The first row will be returned since both is true. I don't know if it will match on both columns, but does it really matter? The second row will be returned since the first 'Anders' is a match and the third row will be returned since 'Andersson' is a match. The last row won't be returned since there is no match.

I don't understand what you want to be returned. Can you give an example to make us understand.

Bozebo

So, what you're saying is MYSQL or is exclusive? unlike PHP? Try xor instead

Wait: actually I'm wrong the.. I am still looking into it

try this page in the mysql documentation: http://dev.mysql.com/doc/refman/4.1/en/non-typed-operators.html

laserlight

So, what you're saying is MYSQL or is exclusive? unlike PHP? Try xor instead

No, this is not the concept of exclusive or. I think ElArZ is referring to the lazy evaluation of OR (and AND) in some common programming languages (C, C++, Java, PHP, etc).

It is the reason why we can write things like:

mysql_query($query) or die(":P");

and yet not have the script die on us if the call to mysql_query() returns a value that can be evaluated to true (i.e., a result resource instead of false).

Like Piranha, I would like to see why ElArZ thinks lazy evaluation is needed in SQL. Concrete examples are preferred.

Sxooter

What you're looking for can be done with either Coalesce or case statement. If one field might return a null, you can coalesce to the second one:

select coalesce(field1,field2) as abc from ...

if field 1 returns null, you get field 2.

OR you can use a case statement.

select case when field1='f' then field2 else field1 end as abc ...