UNION SELECT after LEFT JOIN and JOIN

Question

    SELECT DISTINCT re.*
    FROM `reports` r
    LEFT JOIN `users` u
    ON u.`id_rep` = r.`id`
    JOIN `customers` c
    ON u.`id` = r.`uid`
    WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
    ORDER BY r.date DESC

Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?

Answer 1

Using Union and ORDER BY:

Be explicit with results form first query:

(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')

UNION
(SELECT username,rdate FROM users)


ORDER BY rdate DESC

I assumed the second table had a date column. Otherwise and explicit value could be used.

Answer 2

If I understand the question correctly, yes. Each of the following is OK, though not necessarily efficient:

SELECT ...
   FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x

SELECT ...
   FROM a
   JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...

SELECT ...
   FROM a
   LEFT JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...

SELECT ...
   FROM ( ( SELECT ... ) UNION ( SELECT ... ) ) AS a
   JOIN ( ( SELECT ... ) UNION ( SELECT ... ) ) AS x ON ...

etc. (I may have added more parentheses than necessary.)

More UNION tips:

• Yes, the number of columns needs to be the same.
• The default column names come from the first SELECT.
• If you have a constant string in a column of the first SELECT, that limits the length of the matching string of the other SELECTs. (Simply pad with blanks to 'fix' the problem.)
• If appropriate, use UNION ALL do avoid the de-duping phase, thereby being faster than UNION, which is the same as UNION DISTINCT.