8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

SQL for Beginners (Part 6) : Set Operators

This is the sixth part of a series of articles showing the basics of SQL. In this article we take a look at the SQL set operators available in Oracle.

Related articles.

Setup

You can perform all these queries online for free using SQL Fiddle.

The examples in this article require the following tables to be present.

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;


CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.

Introduction

Set operators allow you to combine the results of multiple separate queries into a single result set.

The following two queries will be used for most of the examples in this article. The first returns the departments 10, 20 and 30. The second returns the departments 20, 30 and 40. As you can see, departments 20 and 30 are common to both result sets.

-- Department 10, 20 and 30.
SELECT department_id, department_name
FROM   departments
WHERE  department_id <= 30;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 ACCOUNTING
           20 RESEARCH
           30 SALES

3 rows selected.

SQL>

--Department 20, 30 and 40.
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           20 RESEARCH
           30 SALES
           40 OPERATIONS

3 rows selected.

SQL>

You will see, these are not real-world examples, but they serve to demonstrate how each of the set operators work.

UNION

The UNION set operator returns all distinct rows selected by either query. That means any duplicate rows will be removed.

In the example below, notice there is only a single row each for departments 20 and 30, rather than two each.

SELECT department_id, department_name
FROM   departments
WHERE  department_id <= 30
UNION
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 ACCOUNTING
           20 RESEARCH
           30 SALES
           40 OPERATIONS

4 rows selected.

SQL>

The removal of duplicates requires extra processing, so you should consider using UNION ALL if possible.

UNION ALL

The UNION ALL set operator returns all rows selected by either query. That means any duplicates will remain in the final result set.

In the example below, notice there are two rows each for departments 20 and 30.

SELECT department_id, department_name
FROM   departments
WHERE  department_id <= 30
UNION ALL
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 ACCOUNTING
           20 RESEARCH
           20 RESEARCH
           30 SALES
           30 SALES
           40 OPERATIONS

6 rows selected.

SQL>

INTERSECT

The INTERSECT set operator returns all distinct rows selected by both queries. That means only those rows common to both queries will be present in the final result set.

In the example below, notice there is one row each for departments 20 and 30, as both these appear in the result sets for their respective queries.

SELECT department_id, department_name
FROM   departments
WHERE  department_id <= 30
INTERSECT
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           20 RESEARCH
           30 SALES

2 rows selected.

SQL>

MINUS

The MINUS set operator returns all distinct rows selected by the first query but not the second. This is functionally equivalent to the ANSI set operator EXCEPT DISTINCT.

In the example below, the first query would return departments 10, 20, 30, but departments 20 and 30 are removed because they are returned by the second query. This leaves a single rows for department 10.

SELECT department_id, department_name
FROM   departments
WHERE  department_id <= 30
MINUS
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAM
------------- --------------
           10 ACCOUNTING

1 row selected.

SQL>

ORDER BY

The ORDER BY clause is applied to all rows returned in the final result set. Columns in the ORDER BY clause can be referenced by column names or column aliases present in the first query of the statement, as these carry through to the final result set. Typically, you will see people use the column position as it is less confusing when the data is sourced from different locations for each query block.

-- Column name.
SELECT employee_id, employee_name
FROM   employees
WHERE  department_id = 10
UNION ALL
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_NAME
----------- --------------
         20 RESEARCH
         30 SALES
         40 OPERATIONS
       7782 CLARK
       7839 KING
       7934 MILLER

6 rows selected.

SQL>


-- Column Alias
SELECT employee_id AS emp_id, employee_name
FROM   employees
WHERE  department_id = 10
UNION ALL
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY emp_id;

    EMP_ID EMPLOYEE_NAME
---------- --------------
        20 RESEARCH
        30 SALES
        40 OPERATIONS
      7782 CLARK
      7839 KING
      7934 MILLER

6 rows selected.

SQL>


-- Column position
SELECT employee_id, employee_name
FROM   employees
WHERE  department_id = 10
UNION ALL
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY 1;

EMPLOYEE_ID EMPLOYEE_NAME
----------- --------------
         20 RESEARCH
         30 SALES
         40 OPERATIONS
       7782 CLARK
       7839 KING
       7934 MILLER

6 rows selected.

SQL>

Referencing column names or column aliases in later queries is not allowed.

-- Wrong column name.
SELECT employee_id, employee_name
FROM   employees
WHERE  department_id = 10
UNION ALL
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY department_id;

ORDER BY department_id
         *
ERROR at line 8:
ORA-00904: "DEPARTMENT_ID": invalid identifier

SQL>


-- Wrong column alias
SELECT employee_id, employee_name
FROM   employees
WHERE  department_id = 10
UNION ALL
SELECT department_id AS dept_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY dept_id;

ORDER BY dept_id
         *
ERROR at line 8:
ORA-00904: "DEPT_ID": invalid identifier


SQL>

Data Types

The datatypes must match for the same column position in each query. Some implicit conversions will be possible between some data types, for example CHAR/VARCHAR2, or conversion between some numeric types.

In the following example we convert the EMPLOYEE_ID column to a VARCHAR2 using the TO_CHAR function, which breaks the statement.

SELECT TO_CHAR(employee_id) AS employee_id, employee_name
FROM   employees
WHERE  department_id = 10
UNION ALL
SELECT department_id, department_name
FROM   departments
WHERE  department_id >= 20
ORDER BY 1;

SELECT TO_CHAR(employee_id) AS employee_id, employee_name
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

SQL>

Updates from Oracle 21c Onward

In previous releases the ALL keyword was added to UNION to prevent the removal of duplicate values, and thereby improve performance. In Oracle 21c the ALL keyword can also be added to the MINUS and INTERSECT operators, so their operations are on a per-row basis, rather than a distinct-row basis. Oracle 21c also introduces EXCEPT and EXCEPT ALL operators, which are functionally equivalent to MINUS and MINUS ALL respectively. You can find out more about these changes in the following article.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.