How to use SQL UNION operation

Question

  SELECT a.*

  FROM tableA AS a

  LEFT JOIN tableC AS c
  ON c.id = a.catid

  LEFT JOIN tableD AS d
  ON d.id = a.created_by

  INNER JOIN tableE AS e
  ON e.content_id = a.id

  WHERE a.access IN (1,1,5)
  AND c.access IN (1,1,5)

I am trying to use UNION ALL/ UNION operation instead of IN operation in SQL. I am having troubles to figure out how to convert the above query to use UNION. How Can I convert the above query to use UNION

Can you clarify what you are asking? Does the query work? Provide sample data and expected results. — Gordon Linoff, Jun 14 '14 at 12:44
I don't see where you would use `UNION` instead of `IN`. What are you trying to accomplish? — John Saunders, Jun 14 '14 at 12:45
I want to know how to convert this query to use UNION operation instead of IN operation — Nadeeshaan, Jun 14 '14 at 12:46
There is no need to convert this query to use `UNION` instead of `IN`, if the query is running fine with `IN`. — Gordon Linoff, Jun 14 '14 at 12:47
I was thinking that UNION operation is faster than the IN operation. Which is the faster one? — Nadeeshaan, Jun 14 '14 at 12:49
Note: the `AND c.access IN (1,1,5)` will effectively tun the `LEFT JOIN tableC AS c` INTO a plain JOIN (because `c.access` cannot be NULL NOTE2: `AND c.access IN (1,1,5)` IS equivalent to `AND c.access IN (1,5)` (similar for a.acccess ) — wildplasser, Jun 14 '14 at 12:53
Is this a real query? If that is the case I think it is possible to simplify it a bit — Lennart - Slava Ukraini, Jun 14 '14 at 12:54
There are some cases where `union all` (and sometimes even `union`) is can perform better than a complex query. This query is unlikely to be one of those cases. — Gordon Linoff, Jun 14 '14 at 12:58

score 0 · Accepted Answer · answered Jun 14 '14 at 12:58

Maybe you need something like this:

(
SELECT a.*
FROM tableA AS a
LEFT JOIN tableC AS c
ON c.id = a.catid
WHERE a.access = 1
AND c.access = 1
)

UNION ALL

(
SELECT a.*
FROM tableA AS a
LEFT JOIN tableC AS c
ON c.id = a.catid
WHERE a.access = 5
AND c.access = 5
)

UNION ALL

(
SELECT a.*
FROM tableA AS a
LEFT JOIN tableD AS d
ON d.id = a.created_by
INNER JOIN tableE AS e
ON e.content_id = a.id
)

How to use SQL UNION operation

1 Answers1