Although there is a sql ansi standard each database vendor has its own proprietary extensions and MySQL is no different. These changes, although small, are frustrating enough to cost hours of lost time and productivity. One of the issues I lost some time on was how to do an "if exists ... then ...else" statement in a stored procedure, basically to have some dynamic sql execute an update or an insert query depending on whether the record already existed or not.

MySQL's Way - "INSERT INTO... ON DUPLICATE KEY UPDATE"

MySQL does not support the "Exist" function so that approach is not available in this case. One way to address this would be to do an aggregate query and see if it returns a results eg:

Delimiter $$
Create Procedure example_proc()
Begin
  Declare var INT;
  Select count(*) into var from users where username ='some user';
  if var then
     Update users set email='bbbb',firstname='cccc',lastname='dddd' where username='aaa';
  else
    Insert into users (username,email.firstname.lastname) value('aaa','bbb','ccc','dddd');
  end if;
end;
$$

 But this is a horribly verbose way of doing something. If Exists, when used in this context, is much more succinct. However MySQL does have a syntax that allows the insert or update to be done in one statement, neatly and to the point. The "Insert into .... on Duplicate key update " statement. The statement basically ties to insert the record and if a matching primary key is found it will run an update instead. So the above example could be written like this

Insert into users (username,email.firstname.lastname) value('aaa','bbb','ccc','dddd') on duplicate key update email='bbbb',firstname='cccc',lastname='dddd;

Notice that we do not include the username in the list of fields to be updated. This is because we have set the username up as the primary key on the users table, which the statement uses to find the row to update.

 Not as Versatile as Exists Function

The Exists function is a lot more versatile than just being used for finding if a single record exists or not. Its main advantage is that it returns from a query as soon as any 1 matching record is found, which is more efficient than iterating over the entire table to find all instances of a particular query. In the use case above there is no advantage that Exists has, as by definition, there can only be 1 record with a specific primary key. So for this subset of the Exists function functionality MySQL provides a convenient  statement that can be used.