Setting Yourself Up for Selective Results
May 1, 2016 | 13 minute readPart 3 in a second series on the basics of the relational database and SQL
By Melanie CaffreyMay/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 CombinationSuppose 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 <strong>UNION</strong>
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 NowIf 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 <strong>UNION ALL</strong>
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.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 <strong>MINUS</strong>
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 <strong>MINUS</strong>
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.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 columnsAdditionally, 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.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.
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
Melanie Caffrey
Melanie Caffrey is a senior development manager at Oracle. She is a coauthor of Beginning Oracle SQL for Oracle Database 12c (Apress, 2014), Expert PL/SQL Practices for Oracle Developers and DBAs (Apress, 2011), and Expert Oracle Practices: Oracle Database Administration from the Oak Table (Apress, 2010).