DBA Hub

📋Steps in this guide1/9

SQL for Beginners (Part 6) : Set Operators

In this article we take a look at the SQL set operators available in Oracle.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
--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;
2

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. You will see, these are not real-world examples, but they serve to demonstrate how each of the set operators work.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 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>
3

UNION

The 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. The removal of duplicates requires extra processing, so you should consider using if possible.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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>
4

UNION ALL

The 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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>
5

INTERSECT

The 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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>
6

MINUS

The set operator returns all distinct rows selected by the first query but not the second. This is functionally equivalent to the ANSI set operator . 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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>
7

ORDER BY

The clause is applied to all rows returned in the final result set. Columns in the 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. Referencing column names or column aliases in later queries is not allowed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- 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>

-- 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>
8

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 / , or conversion between some numeric types. In the following example we convert the EMPLOYEE_ID column to a using the function, which breaks the statement.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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>
9

Updates from Oracle 21c Onward

In previous releases the keyword was added to to prevent the removal of duplicate values, and thereby improve performance. In Oracle 21c the keyword can also be added to the and operators, so their operations are on a per-row basis, rather than a distinct-row basis. Oracle 21c also introduces and operators, which are functionally equivalent to and respectively. You can find out more about these changes in the following article. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!