[SOLVED] Alternative to UNION clause

Alternative to UNION clause

I'm trying to teach myself and better understand alternative methods to a UNION and discover when I can use joins.

As I'm playing with this I can't seem to get what I want without a UNION. Is it possible to write this in a single query?

SELECT DISTINCT a.id
  FROM table1 a, table2 b
 WHERE a.id = b.id
       AND a.ind IS NULL
       AND b.year >= '2017'
       AND b.code IN ('01','02','03')
       AND b.flag NOT IN ('F','L')
UNION
SELECT DISTINCT a.id
  FROM table1 a, table3 c
 WHERE a.id = c.id
       AND a.ind IS NULL
       AND c.area = 'MAIN'
       AND SYSDATE >= c.start

Thanks in advance for any guidance or help.

Solution

SELECT DISTINCT a.id
FROM table1 a
LEFT JOIN table2 b on b.id = a.id AND b.year >= '2017'
     AND b.code IN ('01', '02', '03') AND b.flag NOT IN ('F', 'L') 
LEFT JOIN table3 c ON a.id = c.id and c.area = 'MAIN' and SYSDATE >= c.start
WHERE a.ind IS NULL
    AND ( b.id IS NOT NULL or c.id IS NOT NULL)

This is one of those things where the old obsolete A,B join syntax really shows it's age, when you have some conditions that must go in a specific ON clause and others that must go in the WHERE clause. It would be very difficult to write this query that way, and even harder to read and understand it later. Better to always write out the full INNER JOIN, LEFT JOIN, etc.