theJava theJava - 1 year ago 59
MySQL Question

SET operations and JOIN operations

I was going through the Learning SQL 2nd Edition book. Is there any difference between the

operations and the
.Are there any situations where you would go with
operations leaving

Correct me if i am wrong, we can do all the things using
what we can do with

Answer Source

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:

A union would be:

       coalesce(t1.col1, t2.col1) as col1,
       . . .  -- repeat this for all columns
from (select distinct coalesce(, as id
      from ((select id
             from t1
            ) full outer join
            (select id
             from t2
     ) driver left outer join
     on = left outer join
     on =

The except and 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:

  1. They are more readable. You and everyone else would probably agree that union and union all are much more readable than the above queries.
  2. The SQL engine understands the set operations, so they are typically going to be more efficient.
  3. It is easy to make a subtle error in the more complicated SQL statement that would radically change the results. For instance, changing the 'full outer join' in the driver table above into 'inner join' is the difference between union and intersect.