UNION inside IF EXISTS statement not working

Question

SELECT A, B, C FROM TUser

UNION

IF EXISTS(SELECT dataUserId FROM TUserData WHERE DataId = @dataId AND UserId = @userId)
  BEGIN
    SELECT @dataUserId  = dataUserId FROM TUserData WHERE DataId = @dataId AND UserId = @userId

    SELECT A, B, C FROM TUser WHERE UserId = dataUserId 
  END

Answer 1

Wrong usage of union. All tables that your are pulling together in a union have to have the same columns. Something more like:

SELECT A, B, C FROM TUser

UNION

SELECT A, B, C FROM TUser WHERE UserId = dataUserId

Answer 2

That is invalid SQL. It appears that you are trying to write something like a function/stored-procedure.

This is how UNION works (http://www.w3schools.com/SQL/sql_union.asp):

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Both SELECT statements must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Answer 3

You can transform it like that:

SELECT @dataUserId  = dataUserId FROM TUserData
WHERE DataId = @dataId AND UserId = @userId

IF (@dataUserId IS NOT NULL)
  BEGIN
    SELECT A, B, C FROM TUser
    UNION
    SELECT A, B, C FROM TUser WHERE UserId = @dataUserId 
  END
ELSE
  SELECT A, B, C FROM TUser

Answer 4

You could use exists in WHERE clause instead of the IF statement:

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2 
     WHERE EXISTS (SELECT columns FROM table_name3)

Answer 5

You can't use an if statement inside a union like that.

Instead of using an if exists you can simply use an inner join:

select A, B, C from TUser

union

select u.A, u.B, u.C from TUser u
inner join TUserData t on t.dataUserId = u.UserId
where t.DataId = @dataId and t.UserId = @userId

There is one small difference when using an inner join, though. If you would happen to match more than one record from the TUserData table, it will not randomly throw away all results except one as the select @dataUserId=... would, instead if will return the result from each match.

(Note that the union is pointless in it's current form. As you get all records from the table in the first query, anything that you get in the second query will only be duplicates, but I suppose that you just simplified the first query and that it doesn't actually get everything from the table.)

Answer 6

SELECT A, B, C 
FROM TUser

UNION

SELECT u.A, u.B, u.C 
FROM TUser AS u 
  JOIN TUserData AS d ON u.UserId = d.dataUserId
WHERE d.UserId = @userId 
  AND d.DataId = @dataId