I was going through the Learning SQL 2nd Edition book. Is there any difference between the
No, you cannot do all the things with a
join that you can do with the set operations. However, if two tables have the same structure and have unique identifiers on each, then you can:
union would be:
select driver.id, coalesce(t1.col1, t2.col1) as col1, . . . -- repeat this for all columns from (select distinct coalesce(t1.id, t2.id) as id from ((select id from t1 ) full outer join (select id from t2 ) ) ) driver left outer join t1 on t1.id = driver.id left outer join t2 on t2.id = driver.id
intersect can be approached in the same way, but putting conditions on the
full outer join in the driver table.
In fact, you can extend this idea to not require a unique id, just assuming that the rows are unique in each table. In that case, the joins are more complicated, because you have to include all columns, and also take into account NULL values in the columns.
That said, the
set operations have several advantages:
union allare much more readable than the above queries.