Using MySQL "IF EXISTS"

Question

Here are two statements that I would like to work but that return error messages:

IF EXISTS (SELECT * FROM gdata_calendars WHERE `group` = ? AND id = ?) SELECT 1 ELSE SELECT 0 

and

 IF ((SELECT COUNT(*) FROM gdata_calendars WHERE `group` = ? AND id = ?) > 0) SELECT 1 ELSE SELECT 0; 

There are question marks because I use parameterized, prepared instructions with PHP PDO. However, I also tried to do this with the data manually, and it really doesn't work.

Although I would like to know why each of them does not work, I would prefer to use the first query if it can be made to work.

Answer 1

You cannot use the IF OUTSIDE control block. Thus, this affects both of your requests.

Turn the EXISTS clause into a subquery instead of the IF function

 SELECT IF( EXISTS( SELECT * FROM gdata_calendars WHERE `group` = ? AND id = ?), 1, 0) 

In fact, booleans return as 1 or 0

 SELECT EXISTS( SELECT * FROM gdata_calendars WHERE `group` = ? AND id = ?) 

Answer 2

I found the RichardTheKiwi example quite informative.

Just to suggest a different approach if you are looking for something like IF EXISTS (SELECT 1 ..) THEN ...

- what can I write in MSSQL

 IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='') BEGIN SELECT TableID FROM Table WHERE FieldValue='' END ELSE BEGIN INSERT INTO TABLE(FieldValue) VALUES('') SELECT SCOPE_IDENTITY() AS TableID END 

- rewritten for MySQL

 IF (SELECT 1 = 1 FROM Table WHERE FieldValue='') THEN BEGIN SELECT TableID FROM Table WHERE FieldValue=''; END; ELSE BEGIN INSERT INTO Table (FieldValue) VALUES(''); SELECT LAST_INSERT_ID() AS TableID; END; END IF; 

Answer 3

 if exists(select * from db1.table1 where sno=1 ) begin select * from db1.table1 where sno=1 end else if (select * from db2.table1 where sno=1 ) begin select * from db2.table1 where sno=1 end else begin print 'the record does not exits' end