Team Fly

Page 76

NOTE
Please be aware that the intersect set operator can introduce major performance problems. If you are venturing down this path, weigh the alternatives first.

minus

The minus set function returns all the rows in the first table minus the rows in the first table that are also in the second table. The order of the tables is important. Pay close attention to the order of the tables and the different results in these two query examples:

SQL> select * from x
  2  minus
  3  select * from y;

COL
---
1
2
3
4

SQL> select * from y
  2  minus
  3  select * from x;

COL
---
7

Project 2-4 Using the union Function in Your SQL

During our discussion of Oracle outer joins and the associated Project 2.1, we mentioned that a full outer join wasn't readily available using the Oracle syntax. We went on to mention that when we learned about the union set function, we'd take a moment to revisit creating an outer join without using ANSI SQL syntax.

Step by Step

Let's first recall the Oracle right outer join and left outer join examples we were working on in Project 2.1.

1. Use the right outer join example from Project 2.1:

Team Fly