Using the Set Operators

Using the Set Operators

 UNION and UNION ALL operator
 INTERSECT operator
 MINUS operator
 Matching the SELECT statements
 Using the ORDER BY clause in set operations
Some rules for set operators.
 The expressions in the SELECT lists must match in number.
 The data type of each column in the second query must match the data type of its
corresponding column in the first query.
 Parentheses can be used to alter the sequence of execution.
 ORDER BY clause can appear only at the very end of the statement.
 Duplicate rows are automatically eliminated except in UNION ALL.
 Column names from the first query appear in the result.
 The output is sorted in ascending order by default except in UNION ALL.
UNION:-returns all rows returned by either query eliminating duplicates or returns only the
distinct values.
Example: - select dept_id from EMP UNION select dept_id from DEPT:

DEPT_ID
10
15
20
24
25
30

UNION ALL: returns all rows returned by either query including duplicates.
Example:-select dept_id from EMP UNION ALL select dept_id from dept;

1 Assisted By FIKRU T.
Using the Set Operators

DEPT_ID
10
20
15
15
30
10
20
25
30
24

INTERSECT: returns all distinct rows or columns rows returned by both queries.
Note: A row must exist in both query outputs to be returned in the final result.

Example:-select dept_id from EMP INTERSECT select dept_id from dept;

DEPT_ID
10
20
30

MINUS: produce rows returned by the first query but not by the second.

Example1: select dept_id from EMP MINUS select dept_id from dept;

2 Assisted By FIKRU T.
Using the Set Operators

DEPT_ID

15

Example2: select dept_id from dept MINUS select dept_id from EMP;

DEPT_ID
24
25

Matching the SELECT Statements

 Using the UNION operator, display the location ID, department name, and the state
where it is located.
 You must match the data type (using the TO_CHAR function or any other conversion
functions) when columns do not exist in one or the other table.

Example: SELECT dept_id,empname,null"DEPT_NAME" ,salary,job,0"LOCATION_ID"

FROM emp

UNION

SELECT dept_id,null,dept_name,0,null,location_id

FROM dept;

DEPT_ID EMPNAME DEPT_NAME SALARY JOB LOCATION_ID

10 CLARK - 2450 MANAGER 0
10 - Adminstration 0 - 1700
15 KING - 5000 PRESIDENT 0

3 Assisted By FIKRU T.
Using the Set Operators

15 TURNER - 1500 SALESMAN 0

20 SCOTT - 300 ANALYST 0
20 - Computer science 0 - 1800
24 - Accounting 0 - 1700
25 - Information Tech 0 - 1500
30 ADAMS - 1100 CLERK 0
30 - Marketing 0 - 1400
NOTE: You can use 0 and null for unmatched column datatype in case of number datatype and
null incase of characters or vaechar2.

Using the ORDER BY Clause in Set Operations

 The ORDER BY clause can appear only once at the end of the compound query.
 Component queries cannot have individual ORDER BY clauses.
 ORDER BY clause recognizes only the columns of the first SELECT query.
 By default, the first column of the first SELECT query is used to sort the output in an
ascending order.

Example: - SELECT dept_id,empname,job,salary,null"Dept_name",0"location_id" FROM

emp

UNION

SELECT dept_id,null,null,0,dept_name,location_id FROM dept order by empname;

DEPT_ID EMPNAME JOB SALARY DEPT_NAME LOCATION_ID

30 ADAMS CLERK 1100 - 0
10 CLARK MANAGER 2450 - 0
15 KING PRESIDENT 5000 - 0
20 SCOTT ANALYST 300 - 0
15 TURNER SALESMAN 1500 - 0
10 - - 0 Adminstration 1700
20 - - 0 Computer science 1800
24 - - 0 Accounting 1700
25 - - 0 Information Tech 1500
30 - - 0 Marketing 1400

OPERATORS IN ORACLE

Examples of Arithmetic operators

4 Assisted By FIKRU T.
Using the Set Operators

Addition: Returns the sum of the specified rows in the columns

Example: Select salary + commission"addition"from EMP;

ADDITION
2950
800
5500
2000
1600

Subtraction: returns the difference of the specified rows in the column.

Example: Select salary-commission "SUBSTRACTION" from EMP;

SUBSTRACTI
ON
1950
-200
4500
1000
600

Multiplication: returns the product of the specified rows in the column.

Example: Select salary * commission "PRODUCT “from EMP;

PRODUCT
1225000
150000
2500000
750000
550000

Division: returns the quotient of the specified rows from the specified columns.

Example: select salary/commission "QOUTIENT "from EMP

5 Assisted By FIKRU T.
Using the Set Operators

QOUTI ENT
4.9
.6
10
3
2.2

Examples of Relational/Comparison Operators

Relational operators include <,>, <=,>=, <>

Example: select * from EMP where salary <3000;

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20

7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 15

7876 ADAMS CLERK 7788 23-MAY-87 1100 500 30

Example: select * from EMP where salary >2000;

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7839 KING PRESIDEN 7788 17-NOV-81 5000 500 15

T

Example: select * from EMP where salary <= 1500;

6 Assisted By FIKRU T.
Using the Set Operators

EMPNO EMPNAM JOB MGR HIREDAT SALARY COMMISSIO DEPT_I

E E N D

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20

7844 TURNER SALESMA 7698 08-SEP-81 1500 500 15

N

7876 ADAMS CLERK 7788 23-MAY-87 1100 500 30

Example: select * from EMP where salary >= 2450;

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSIO DEPT_ID

N
7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7839 KING PRESIDEN 7788 17-NOV-81 5000 500 15

T

Example: select * from EMP where salary <> 1100;

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20

7839 KING PRESIDENT 7788 17-NOV-81 5000 500 15

7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 15

LIKE Operator

7 Assisted By FIKRU T.
Using the Set Operators

Like operator is a pattern matching operator. ‘%’ is used to denote any number of unknown
characters.

Example: select empname,empno,job, hiredate,salary from emp where empname LIKE '%K';

EMPNAME EMPNO JOB HIREDATE SALARY

CLARK 7782 MANAGER 09-JUN-81 2450

Will match any empname that ends in a ‘K’

Example: select empname, empno, job, hiredate, commission from EMP where empname
LIKE 'K%'

EMPNAME EMPNO JOB HIREDATE COMMISSION

KING 7839 PRESIDENT 17-NOV-81 500
Will match any empname that start with ‘K’

Example: select * from EMP where JOB LIKE '%NA%';

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20

This is to retrieve rows where job contains the word ‘NA’ embedded in it.

Example: select * from EMP where empname LIKE 'S%T';

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20

To retrieve rows where the empname starts with ’S’ and ends with ‘T’.

Logical Operators

AND: ensures that rows satisfying both the conditions are selected.
8 Assisted By FIKRU T.
Using the Set Operators

Example: select * from EMP where empno=7788 AND empname='SCOTT';

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20

OR: ensures that a row satisfying any one of the conditions is retrieved.

Example: select * from EMP where empno=7782 OR empname='KING';

EMPNO EMPNAM JOB MGR HIREDAT SALARY COMMISSIO DEPT_ID

E E N
7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10
7839 KING PRESIDEN 7788 17-NOV-81 5000 500 15
T

NOT: ensures that a row not satisfying the condition specified in the query is retrieved.

Example: select * from EMP where NOT empname ='ADAMS';

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20
7839 KING PRESIDENT 7788 17-NOV-81 5000 500 15

7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 15

IN: one of a set of values tells the DBMS, that one of the values must be in the
specified value list.

Example: select * from EMP where empno IN (7782, 7788, 7876);

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGE 7839 09-JUN-81 2450 500 10

R
7788 SCOTT ANALYST 7566 19-APR-87 300 500 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 500 30

9 Assisted By FIKRU T.
Using the Set Operators

Example: select * from EMP where JOB IN ('CLERK','PRESIDENT');

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7839 KING PRESIDENT 7788 17-NOV-81 5000 500 15

7876 ADAMS CLERK 7788 23-MAY-87 1100 500 30

NOT IN: values must not be in the specified values in the specified list.

Example: select * from EMP where JOB NOT IN ('CLERK','PRESIDENT');

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 15

BETWEEN: checks when a field is between two values.

Example: select * from EMP where salary BETWEEN 1500 AND 2450;

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 15

NOT BETWEEN: used to exclude the values between our ranges.

Example: select * from EMP where salary NOT BETWEEN 1500 AND 2450;

10 Assisted By FIKRU T.
Using the Set Operators

EMPNO EMPNAME JOB MGR HIREDATE SALARY COMMISSION DEPT_ID

7788 SCOTT ANALYST 7566 19-APR-87 300 500 20

7839 KING PRESIDEN 7788 17-NOV-81 5000 500 15
T
7876 ADAMS CLERK 7788 23-MAY-87 1100 500 30

CONCATINATION: one string operator. || for string concatenation

 The operator concatenates the second string to the ends of the first.

Example: select empname ||' (' ||JOB||' )'"CONCATINATION" from EMP;

CONCATINATION
CLARK (MANAGER )
SCOTT (ANALYST )
KING (PRESIDENT )
TURNER (SALESMAN )
ADAMS (CLERK )

11 Assisted By FIKRU T.