Usage of MySQL's "IF EXISTS"

Here are two statements that I'd like to work, but which 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; 

The question marks are there because I use parametrized, prepared, statements with PHP's PDO. However, I have also tried executing this with data manually, and it really does not work.

While I'd like to know why each of them doesn't work, I would prefer to use the first query if it can be made to work.

6 Answers

You cannot use IF control block OUTSIDE of functions. So that affects both of your queries.

Turn the EXISTS clause into a subquery instead within an IF function

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

In fact, booleans are returned as 1 or 0

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

I found the example RichardTheKiwi quite informative.

Just to offer another approach if you're looking for something like IF EXISTS (SELECT 1 ..) THEN ...

-- what I might 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; 

The accepted answer works well and one can also just use the

If Exists (...) Then ... End If; 

syntax in Mysql procedures (if acceptable for circumstance) and it will behave as desired/expected. Here's a link to a more thorough source/description:

One problem with the solution by @SnowyR is that it does not really behave like "If Exists" in that the (Select 1 = 1 ...) subquery could return more than one row in some circumstances and so it gives an error. I don't have permissions to respond to that answer directly so I thought I'd mention it here in case it saves someone else the trouble I experienced and so others might know that it is not an equivalent solution to MSSQLServer "if exists"!

If your table has an auto-incrementing primary key, you can use REPLACE INTO ... VALUES

SELECT @id := id FROM tableName WHERE fieldName='criteria value' LIMIT 1; REPLACE INTO tableName(id, fieldName, col1, col2) VALUES (@id, 'criteria value', 'value1', 'value2') 

If the select statement returns NULL, then a new row is inserted. Otherwise, if a row is found, it will update the row with key @id.

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

For Detail explanation you can visit here

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