MySQL IF EXISTS not working

Question

Trying to use IF EXISTS to automatically choose which table to select a record from.

I just get the following error message with the code below.

Where am I going wrong?

IF EXISTS (SELECT 1 FROM Users WHERE Reference='USR00000007')
    SELECT * FROM Users WHERE Reference='USR00000007'
ELSE
    SELECT * FROM Staff WHERE Reference='USR00000007';

Error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT 1 FROM Users WHERE Reference='USR00000007') SELECT * FROM' at line 1

https://blogs.msdn.microsoft.com/miah/2008/02/17/sql-if-exists-update-else-insert/ — Rs2845, Mar 21 '16 at 16:14
(by the way my knowledge of SQL is very low, I'm learning as I am going with this project) — Rs2845, Mar 21 '16 at 16:14
why are you linking a post to how to use `IF EXISTS` for SQL Server when you are using MySQL? — Lamak, Mar 21 '16 at 16:15
Possible duplicate of [Usage of MySQL's "IF EXISTS"](http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists) — Hanky Panky, Mar 21 '16 at 16:16
@Rs2845 Certain things like control flow, date manipulation, string manipulation are different from db to db. But even with ANSI SQL mysql is much different than say MS SQL, Oracle, DB2 or Postgres — Conrad Frix, Mar 21 '16 at 16:21
You can't start query from IF, usually start form Select. What result would you like to see? — Adam Silenko, Mar 21 '16 at 16:24
@AdamSilenko Essentially I want to return all columns from either table where a user record of Reference=xxx is present — Rs2845, Mar 21 '16 at 16:34

score 3 · Answer 1 · answered Mar 21 '16 at 16:15

3

The problem is in MySQL. The IF -- as control flow -- only works in programming blocks such as stored procedures, stored functions, and triggers.

Assuming the columns are the same in both tables, you can do what you want as a single query:

SELECT u.*
FROM Users u
WHERE u.Reference = 'USR00000007'
UNION ALL
SELECT s
FROM Staff s
WHERE s.Reference = 'USR00000007' AND
      NOT EXISTS (SELECT 1 FROM Users u2 WHERE u2.Reference = 'USR00000007')

answered Mar 21 '16 at 16:15

Gordon Linoff

1,242,037
58
646
786

Just to mention, I need all the rows from either table returning. The staff table has 2 less columns. Will I need to list the exact ones I want manually as opposed to doing u.* and s.*? – Rs2845 Mar 21 '16 at 16:17
are you sure he have same fields on Users and Staff? – Adam Silenko Mar 21 '16 at 16:20
Not all of them are the same. The users have additional fields, whereas staff have some different columns. – Rs2845 Mar 21 '16 at 16:28
@Rs2845 . . . Then you cannot do what you want with a single query, unless you include placeholders for the columns that are different in the two tables. – Gordon Linoff Mar 22 '16 at 03:05

Adam Silenko · Answer 2 · 2016-03-21T19:06:10.730

if you want union of two query, both mast have same number of fields (if you need, you can add null fields to query) and fields should have similar type, so try this:

SELECT Users.id as user_id, null as staff_id, some_other_number_field, some_other_char_field
FROM Users 
WHERE Reference='USR00000007'
SELECT null , Staff.id, some_other_number_field, some_other_char_field 
FROM Staff 
WHERE Reference='USR00000007'
and not EXISTS (SELECT 1 FROM Users WHERE Reference='USR00000007');

maybe you need query like this, to always get only users for Reference:

SELECT * 
FROM Users 
WHERE (Reference='USR00000007'
or exists (
  SELECT 1 
  FROM Staff 
  WHERE Reference='USR00000007'
  and Staff.user_id = Users.id));

but you must have join condition similar to "and Staff.user_id = Users.id"

This code worked to successfully retrieve the correct record from the Users table, but not the staff table. I am so surprised that MySQL doesn't have a better way of handling conditionals. Tempted to just leave it as two separate queries and just live with the PHP if-else statements to handle the condition. — Rs2845, Mar 21 '16 at 20:09

MySQL IF EXISTS not working

2 Answers2