Subscribe

Share

Database, SQL and PL/SQL

Setting Yourself Up for Selective Results

Part 3 in a second series on the basics of the relational database and SQL

By Melanie Caffrey

May/June 2016

This article is the third in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine.

In the previous Beyond SQL 101 article, “Achieve Winning Combinations with Joins and Subqueries,” you learned how outer joins and self-joins help you obtain results that are difficult to get with an ordinary equijoin. You saw, too, that the results of outer joins include the rows returned by an equijoin plus certain rows from one table for which no rows in the other table satisfy the join condition. You also got an introduction to subqueries, both scalar and correlated, and learned how they enable you to use output from one query as input to another query or SQL statement.

In this article, you will learn about set operators, which take two or more query result sets as input to produce a single result set. The set operators are

  • UNION
  • UNION ALL
  • MINUS
  • INTERSECT

The UNION and UNION ALL set operators combine results. The MINUS set operator subtracts all the rows in the second set from the first set and then performs a DISTINCT operation on the result to return only unique rows. The INTERSECT set operator returns only the distinct rows that are common to both sets.

As input, all set operators use result sets obtained from SELECT statements. To produce correct results, use of set operators must adhere strictly to two rules:

  • All of the SELECT statements must contain the same number of columns.
  • The columns compared between result sets must have the same datatype. (The VARCHAR2 and CHAR character datatypes are considered datatype-compatible.)

In addition to finding out how to use the set operators, you’ll learn about their execution order, how they compare with equijoins, and how they evaluate NULL values in columns.

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 1 (12.1.0.2.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.)

A Distinctive Combination

Suppose your business requirement is to obtain a report that combines two sets of data about your company’s employees to help specify bonuses and raises. One set must contain information about all employees who report to a manager whose salary exceeds US$95,000, and the second set must contain information about all employees hired after January 1, 2008. If the record for any employee is in both sets, you must include that record only once in the report.

You can use the UNION set operator to obtain the combined result set, as in the example in Listing 1. The employees Betsy James, Donald Newton, Lori Dovichi, Matthew Michaels, Roger Friedli, and Theresa Wong all meet the first business criterion of reporting to a manager whose salary is greater than US$95,000. The employees Lori Dovichi, Theresa Wong, Thomas Jeffrey, mark leblanc [sic], and michael peterson [sic] all meet the second business criterion of having been hired after January 1, 2008. You can see that although the records for both Lori Dovichi and Theresa Wong meet both of the specified report criteria, their records appear only once (which is also a requirement of the report).

Code Listing 1: Obtaining a distinct list of employees with the UNION set operator

SQL> set feedback on
SQL> set lines 32000

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  2    from employee emp, employee mgr
  3   where emp.manager = mgr.employee_id
  4     and emp.manager in (select employee_id
  5                           from employee
  6                          where salary > 95000)
  7  UNION
  8 select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  9   from employee emp, employee mgr
 10  where emp.manager = mgr.employee_id 
 11    and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY');

Employee Name     Manager Name     Emp_Salary Mgr_Salary Emp_Hired
————————————————— ———————————————— —————————— —————————— —————————
Betsy James       Emily Eckhardt        60000     100000 16-MAY-07
Donald Newton     Emily Eckhardt        80000     100000 24-SEP-06
Lori Dovichi      Emily Eckhardt                  100000 07-JUL-11
Matthew Michaels  Emily Eckhardt        70000     100000 16-MAY-07
Roger Friedli     Emily Eckhardt        60000     100000 16-MAY-07
Theresa Wong      Thomas Jeffrey        70000     300000 27-FEB-10
Thomas Jeffrey                         300000            27-FEB-10
mark leblanc      michael peterson      65000      90000 06-MAR-09
michael peterson                        90000            03-NOV-08

9 rows selected.

In Listing 1, the UNION set operator combines the result sets from both SELECT statements and returns a distinct result set. The duplicate rows are not returned, because the default behavior for UNION is that duplicate rows are removed from the result.

When you use a UNION set operator, the results can appear to be ordered (as they are in Listing 1), but that’s not always the case. Oracle Database can perform a DISTINCT operation on a set in other ways (such as a hash operation) that can result in unordered data. Also, the sort a UNION set operator uses is a simple binary sort, which doesn’t necessarily sort in the same order as an ORDER BY. The sort used to perform a DISTINCT operation on data does not necessarily respect the character-set sorting order; it assures only uniqueness. The only predictable way to get ordered data from any SQL database is to use an ORDER BY clause.

All Together Now

If your goal is to return all rows, including any duplicates, you can use the UNION set operator and add columns or pseudocolumns to your SELECT lists to ensure each record’s uniqueness. A pseudocolumn behaves like a table column but isn’t stored in the table; you can select from pseudocolumns but not insert, update, or delete their values.

Listing 2 demonstrates the addition of pseudocolumns to the queries shown in Listing 1. In Listing 2, the ‘by manager salary’ text literal is added to the first SELECT list as a pseudocolumn and ‘by hire date’ is added to the second SELECT list as a pseudocolumn. Note also that if the records for Lori Dovichi and Theresa Wong were in either data set twice with different EMPLOYEE_ID values, they would still appear only once in the final result set, because EMPLOYEE_ID is not part of either SELECT list in the set operation. In such a case, you’d want to include the EMPLOYEE_ID values to meet the specified report criteria.

Code Listing 2: Obtaining a distinct list with the UNION operator and pseudocolumns

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired", 
'by manager salary'
  2   from employee emp, employee mgr
  3  where emp.manager = mgr.employee_id
  4    and emp.manager in (select employee_id
  5                          from employee
  6                         where salary > 95000)
  7  UNION
  8  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired", 
'by hire date'
  9    from employee emp, employee mgr
 10   where emp.manager = mgr.employee_id 
 11     and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY';

Employee Name    Manager Name   Emp_Salary Mgr_Salary Emp_Hired 'BYMANAGERSALARY'
———————————————— —————————————— —————————— —————————— ————————— ————————————————
Betsy James      Emily Eckhardt     60000    100000   16-MAY-07 by manager salary
Donald Newton    Emily Eckhardt     80000    100000   24-SEP-06 by manager salary
Lori Dovichi     Emily Eckhardt              100000   07-JUL-11 by hire date
Lori Dovichi     Emily Eckhardt              100000   07-JUL-11 by manager salary
Matthew Michaels Emily Eckhardt     70000    100000   16-MAY-07 by manager salary
Roger Friedli    Emily Eckhardt     60000    100000   16-MAY-07 by manager salary
Theresa Wong     Thomas Jeffrey     70000    300000   27-FEB-10 by hire date
Theresa Wong     Thomas Jeffrey     70000    300000   27-FEB-10 by manager salary
Thomas Jeffrey                               300000   27-FEB-10 by hire date
mark leblanc     michael peterson   65000    90000    06-MAR-09 by hire date
michael peterson                    90000             03-NOV-08 by hire date
11 rows selected.

Alternatively, you can use the UNION ALL set operator to ensure that all records, whether they are duplicates or not, are returned. Listing 3 is nearly identical to Listing 1, with the key difference being that Listing 3 uses the UNION ALL set operator instead of the UNION set operator. The query in Listing 3, like the one in Listing 2, returns the records for Lori Dovichi and Theresa Wong twice.

Code Listing 3: Using UNION ALL to include duplicates

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  2   from employee emp, employee mgr
  3   where emp.manager = mgr.employee_id
  4     and emp.manager in (select employee_id
  5                           from employee
  6                          where salary > 95000)
  7  UNION ALL
  8  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  9    from employee emp, employee mgr
 10   where emp.manager = mgr.employee_id 
 11     and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY');

Employee Name    Manager Name           Emp_Salary Mgr_Salary Emp_Hired
———————————————— —————————————————————— —————————— —————————— —————————
Donald Newton    Emily Eckhardt              80000     100000 24-SEP-06
Matthew Michaels Emily Eckhardt              70000     100000 16-MAY-07
Roger Friedli    Emily Eckhardt              60000     100000 16-MAY-07
Betsy James      Emily Eckhardt              60000     100000 16-MAY-07
Lori Dovichi     Emily Eckhardt                        100000 07-JUL-11
Theresa Wong     Thomas Jeffrey              70000     300000 27-FEB-10
Lori Dovichi     Emily Eckhardt                        100000 07-JUL-11
mark leblanc     michael peterson            65000      90000 06-MAR-09
Theresa Wong     Thomas Jeffrey              70000     300000 27-FEB-10
Thomas Jeffrey                                         300000 27-FEB-10
michael peterson                                        90000 03-NOV-08
11 rows selected.
The Minus Effect

Suppose your business requirement now is to obtain a report that uses the same two business criteria as Listings 1 and 3, except that any employee that meets the second criterion (the person was hired after January 1, 2008) must be excluded from the final result. Consider the query in Listing 4, which uses the MINUS operator. All records that meet the second business criterion are excluded from the result set, including the records for Lori Dovichi and Theresa Wong.

Code Listing 4: The query from Listing 1 employing the MINUS set operator

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  2   from employee emp, employee mgr
  3  where emp.manager = mgr.employee_id
  4    and emp.manager in (select employee_id
  5                          from employee
  6                         where salary > 95000)
  7  MINUS
  8  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  9   from employee emp, employee mgr
 10  where emp.manager = mgr.employee_id 
 11    and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY');  

Employee Name     Manager Name     Emp_Salary Mgr_Salary Emp_Hired
————————————————— ———————————————— —————————— —————————— —————————
Betsy James       Emily Eckhardt        60000     100000 16-MAY-07
Donald Newton     Emily Eckhardt        80000     100000 24-SEP-06
Matthew Michaels  Emily Eckhardt        70000     100000 16-MAY-07
Roger Friedli     Emily Eckhardt        60000     100000 16-MAY-07

4 rows selected.

Subtracting the second result set from the first result set leaves only the employees who were hired before January 1, 2008, and who report to a manager who earns a salary greater than US$95,000. Like the UNION set operator, the MINUS set operator eliminates duplicate records, because it applies an automatic DISTINCT operation to the resulting data set.

The positioning of the SELECT statements in a MINUS set operation is crucial. Listing 5 demonstrates the difference in results received when you reverse the order of which set subtracts from the other. You can also see that employees who meet both criteria are not returned and that the records are still sorted by the first column—the derived Employee Name column.

Code Listing 5: Reversing the order of the SELECT statement sets in Listing 4

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  2    from employee emp, employee mgr
  3   where emp.manager = mgr.employee_id 
  4     and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY')
  5  MINUS
  6  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  7   from employee emp, employee mgr
  8  where emp.manager = mgr.employee_id
  9    and emp.manager in (select employee_id
  10                         from employee
  11                        where salary > 95000);

Employee Name      Manager Name      Emp_Salary Mgr_Salary Emp_Hired
—————————————————— ————————————————— —————————— —————————— —————————
Thomas Jeffrey                           300000            27-FEB-10
mark leblanc       michael peterson       65000      90000 06-MAR-09
michael peterson                          90000            03-NOV-08
3 rows selected.
Coming to a Crossroads

If your business requirement is to obtain a result set consisting only of the records that meet the query criteria for both record sets, use the INTERSECT set operator. Listing 6 takes the query from Listing 4 and replaces the MINUS set operator with the INTERSECT set operator. In this result set, only records that satisfy the conditions for both record sets are returned. Only the employees Lori Dovichi and Theresa Wong were both hired after January 1, 2008, and report to a manager whose salary value exceeds US$95,000.

Code Listing 6: The query from Listing 1 employing the INTERSECT operator

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  2   from employee emp, employee mgr
  3  where emp.manager = mgr.employee_id
  4    and emp.manager in (select employee_id
  5                          from employee
  6                         where salary > 95000)
  7  INTERSECT
  8  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  9   from employee emp, employee mgr
 10  where emp.manager = mgr.employee_id 
 11    and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY');

Employee Name  Manager Name          Emp_Salary Mgr_Salary Emp_Hired
—————————————— ————————————————————— —————————— —————————— —————————
Lori Dovichi   Emily Eckhardt                       100000 07-JUL-11
Theresa Wong   Thomas Jeffrey             70000     300000 27-FEB-10
2 rows selected.

Although an equijoin often produces the same result as an INTERSECT operation, be aware that the INTERSECT operator—like all set operators—operates on all columns across each SELECT list of the set operation. Both the number of columns and the datatypes of the columns included in each SELECT list must match. Consider the query in Listing 7. Because the first SELECT list includes an extra column, EMP.DEPARTMENT_ID, the entire set operation fails. The error message informs you that with the addition of set operators, queries become query blocks and must therefore be evaluated and resolved as a whole set.

Code Listing 7: A set operator with a mismatched number of columns between sets

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired", 
emp.department_id
  2   from employee emp, employee mgr
  3  where emp.manager = mgr.employee_id
  4    and emp.manager in (select employee_id
  5                          from employee
  6                         where salary > 95000)
  7  INTERSECT
  8  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  8   from employee emp, employee mgr
  9  where emp.manager = mgr.employee_id 
 10     and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY');  
select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired", 
emp.department_id
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns

Additionally, NULL values are evaluated differently in equijoins and INTERSECT operations, as shown in Listing 8. First, a copy of the EMPLOYEE table is created for comparison purposes. Then, an equijoin between the EMPLOYEE table and the EMP2 table illustrates that NULL values in one column are never equal to NULL values in another column. By contrast, the operation that immediately follows the equijoin shows that the INTERSECT operator considers NULL values in one column to be equal to NULL values in another column. So, be aware that an INTERSECT operation is not completely equivalent to an equijoin—particularly if any of the columns in the tables being compared allow NULL values.

Code Listing 8: The difference between equijoin and INTERSECT NULL evaluation

SQL> create table emp2 as select * from employee;
Table created.

SQL> select distinct *
  2    from employee e1, emp2 e2
  3   where e1.employee_id   = e2.employee_id
  4     and e1.first_name    = e2.first_name
  5     and e1.last_name     = e2.last_name
  6     and e1.hire_date     = e2.hire_date
  7     and e1.salary        = e2.salary
  8     and e1.manager       = e2.manager
  9     and e1.department_id = e2.department_id;

EMPLOYEE_ID FIRST_NAME  LAST_NAME   HIRE_DATE  SALARY   MANAGER DEPARTMENT_ID
——————————— ——————————— —————————— —————————— ——————— ————————— —————————————
6567        Roger       Friedli     16-MAY-07   60000        28            10
6572        Theresa     Wong        27-FEB-10   70000      6571            30
7895        Matthew     Michaels    16-MAY-07   70000        28            10
6570        mark        leblanc     06-MAR-09   65000      6569            20
1234        Donald      Newton      24-SEP-06   80000        28            10
6568        Betsy       James       16-MAY-07   60000        28            10

6 rows selected.

SQL> select *       
  2    from employee 
  3  intersect
  4  select *
  5    from emp2;

EMPLOYEE_ID FIRST_NAME  LAST_NAME   HIRE_DATE  SALARY   MANAGER DEPARTMENT_ID
——————————— ——————————— ——————————— ————————— ——————— ————————— —————————————
         28 Emily       Eckhardt    07-JUL-04  100000                      10
         37 Frances     Newton      14-SEP-05   75000
       1234 Donald      Newton      24-SEP-06   80000        28            10
       6567 Roger       Friedli     16-MAY-07   60000        28            10
       6568 Betsy       James       16-MAY-07   60000        28            10
       6569 michael     peterson    03-NOV-08   90000                      20
       6570 mark        leblanc     06-MAR-09   65000      6569            20
       6571 Thomas      Jeffrey     27-FEB-10  300000                      30
       6572 Theresa     Wong        27-FEB-10   70000      6571            30
       6573 Lori        Dovichi     07-JUL-11                28            10
       7895 Matthew     Michaels    16-MAY-07   70000        28            10

11 rows selected.

Code Listing 9: A set operation with multiple set operators executing in order

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", 
emp.hire_date "Emp_Hired"
  2   from employee emp, employee mgr
  3  where emp.manager = mgr.employee_id
  4    and emp.manager in (select employee_id
  5                          from employee
  6                         where salary > 95000)
  7  MINUS
  8  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  8   from employee emp, employee mgr
 10   where emp.manager = mgr.employee_id 
 11     and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY')
 12  UNION
 13  select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
 14    from employee emp, employee mgr
 15   where emp.manager = mgr.employee_id
 16     and emp.salary is null;  

Employee Name     Manager Name      Emp_Salary  Mgr_Salary  Emp_Hired
————————————————— ————————————————— —————————— ——————————— ——————————
Betsy James       Emily Eckhardt         60000      100000  16-MAY-07
Donald Newton     Emily Eckhardt         80000      100000  24-SEP-06
Lori Dovichi      Emily Eckhardt                    100000  07-JUL-11
Matthew Michaels  Emily Eckhardt         70000      100000  16-MAY-07
Roger Friedli     Emily Eckhardt         60000      100000  16-MAY-07

5 rows selected.

Listing 9 uses the MINUS operator between the first two SELECT lists to obtain a result set. It then uses the UNION operator between the intermediate result set and the third SELECT list to obtain the final result set. So, although the record for Lori Dovichi is excluded during the MINUS operation, it is included during the UNION operation. Compare the result in Listing 9 with that in Listing 10. Listing 10 uses parentheses to indicate execution order preference. Because the UNION operation is enclosed in parentheses, it is performed first and the MINUS operation is performed last.

Code Listing 10: A set operation using execution order preference

SQL> select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
   2  from employee emp, employee mgr
   3 where emp.manager = mgr.employee_id
   4   and emp.manager in (select employee_id
   5                         from employee
   6                        where salary > 95000)
   7 MINUS
   8 (select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
   9   from employee emp, employee mgr
  10  where emp.manager = mgr.employee_id 
  11    and emp.hire_date > to_date ('01-JAN-2008', 'DD-MON-YYYY')
  12 UNION
  13 select rtrim(emp.first_name||' '||emp.last_name) "Employee Name", 
rtrim(mgr.first_name||' '||mgr.last_name) "Manager Name", 
emp.salary "Emp_Salary", mgr.salary "Mgr_Salary", emp.hire_date "Emp_Hired"
  14   from employee emp, employee mgr
  15  where emp.manager = mgr.employee_id
  16    and emp.salary is null);  

Employee Name     Manager Name        Emp_Salary  Mgr_Salary Emp_Hired
————————————————— ——————————————————— ——————————  —————————— —————————
Betsy James       Emily Eckhardt           60000      100000 16-MAY-07
Donald Newton     Emily Eckhardt           80000      100000 24-SEP-06
Matthew Michaels  Emily Eckhardt           70000      100000 16-MAY-07
Roger Friedli     Emily Eckhardt           60000      100000 16-MAY-07

4 rows selected.
Conclusion

This article introduced you to the Oracle Database set operators and illustrated the types of operations they perform on sets of data. You learned how you can change the execution order by using parentheses for set operation execution preference. And you also discovered how the INTERSECT set operator is similar to and different from an equijoin and that the list of columns in set operation SELECT lists is important. The next article in this series introduces data manipulation language.

Next Steps

READ SQL 101, Parts 1–12.

LEARN more about relational database design and concepts.

DOWNLOAD the sample script for this article.

READ more Beyond SQL 101.

 

Photography by Dmitri Popov, Unsplash