You can use the SQL set operators UNION, MINUS, and INTERSECT to combine the results of two independent query blocks into a single result.

The meanings of these set operators in SQL are listed in the following table.

Operator | Result |
---|---|

Q1 UNION Q2 | All rows occurring in Q1 or in Q2 (or in both) |

Q1 UNION ALL Q2 | As UNION, retaining duplicate rows |

Q1 MINUS Q2 | The rows from Q1, without the rows from Q2 |

Q1 INTERSECT Q2 | The rows occurring in Q1 and in Q2 |

By default, all three set operators suppress duplicate rows in the query result.

UNION ALL operator does not eliminate duplicate rows.

The UNION, MINUS, and INTERSECT operators cannot be applied to any arbitrary set of two queries.

The separate results of queries Q1 and Q2 must be "compatible" in order to use them as arguments to a set operator.

Here the compatibility means the following:

- Q1 and Q2 must select the same number of column expressions.
- The data types of those column expressions must match.

The result table inherits the column names or aliases from Q1.

Q1 cannot contain an ORDER BY clause.

If you specify an ORDER BY clause at the end of the Q2 query, it doesn't refer to Q2, but to the total result of the set operator.

- Write SQL to list who attended the same courses as employee 7008
- Write SQL to list employee who ever taught a course, but never attended a course.
- What is the output: NOT AND and OR
- Write SQL to list the names and initials of all emp, except for R.Jones using AND operator
- Write SQL to Write SQL to list the names and initials of all emp, except for R.Jones using OR operator
- Write SQL to list the number, job, and date of birth of all trainers and sales representatives born before 1960 using OR operator.