MYSQL SELECT WITHIN IF Statement

Question

This is such a simple question but I haven't found an answer anywhere for 2 hours.

How do you use the MYSQL IF statment. No matter what I put in it doesn't work. In SQL Server this is a 5 second job.

Does it need to be within a procedure?

Can you use a SELECT within an IF statement?

Not really interested in the IF function here.

Thanks for any help.

Solution 1

How to use IF statement in select

select if(status>0, 'active', 'inactive') as status from users

Yes, you can use select within IF

Example:

select if((select count(*) from versions) > (select count(*) from groups), true, false) as value

Solution 2

Does it need to be within a procedure?

Yes, you do need to be in a procedure to use an if statement.

Can you use a SELECT within an IF statement?

Yes you can:

drop procedure if exists sp;

create procedure sp () begin
    if ((select 1 /*from whatever table would work*/)=1) then
        select 'it works';
    end if;
end;

call sp;

drop procedure sp;

Solution 3

You can select another field from the same query but using select query might not be supported as query can return multiple values , mysql not supporting row concept in a column.

SELECT IF(`field1`>1,`field2`,`field3`) FROM TABLE1 WHERE 1

here field1,field2,field3 are fields in a same table

Does this help you ?

Solution 4

You hinted at it in your comment, but none of the answers here state it explicitly, so I will:

In MySQL, you cannot use an IF statement outside of the body of a stored procedure. The documentation implies this when it introduces the concept as "the IF statement for stored programs" (my emphasis).

As far as I can see, there is no easy way to control execution flow in statements issued at the command line prompt or via the client (e.g. from PHP), except by querying values, then switching control flow based on those values outside of MySQL (i.e. using PHP's if statement instead of MySQL's). I would love to be corrected on this though :-)

---

You can simulate this on a case-by-case basis using other constructions. For example:

IF x THEN 
  INSERT INTO mytable (mycol)
  VALUES ('foo')
END IF

could become

INSERT INTO mytable (mycol)
SELECT 'foo'
FROM dual
WHERE x

(as per this answer)

---

You can also create, call and then drop a new stored procedure:

DELIMITER \\
CREATE PROCEDURE tmpfunc() BEGIN
  IF x THEN 
    INSERT INTO mytable (mycol)
    VALUES ('foo');
  END IF;
END \\
DELIMITER ;
CALL tmpfunc();
DROP PROCEDURE tmpfunc;

(as per this answer)

Solution 5

you are looking for CASE WHEN? http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

IF is already there in manual http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

I guess @Nanne link is more relevant. Just adding it to the list of links here. http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

View more solutions