6. Set Operators

This chapter explains the set operators that can be used in SQL statements in Altibase.

UNION

Syntax

SELECT statement1 UNION SELECT statement2

Description

This operator is used to output all of the results of two query statements. Note that overlapping results, that is, results common to both queries, as well as duplicate results within each query, will be output only once.

Example

<Query> Display the employee number of every employee who was born in 1980 or later and/or took an order for 100 or fewer items. Remove duplicate employee numbers.

SELECT eno
  FROM employees
 WHERE birth > '800101'
UNION
SELECT eno
  FROM orders
 WHERE qty < 100;

ENO
--------------
4
7
8
12
13
15
20
7 rows selected.

UNION ALL

Syntax

SELECT statement1 UNION ALL SELECT statement2

Description

This operator is used to output all of the results of two query statements. Note that overlapping results, that is, results common to both queries, are output without removing any duplicates.

Example

<Query> Display the employee number of all employees born in 1980 or later, as well as the employee number associated with all orders for 100 or fewer items. Do not remove any duplicate employee numbers.

SELECT eno
  FROM employees
 WHERE birth > '800101'
UNION ALL
SELECT eno
  FROM orders
 WHERE qty < 100;

ENO
--------------
4
7
8
12
13
15
12
20
20
9 rows selected.

INTERSECT

Syntax

SELECT statement1 INTERSECT SELECT statement2

Description

The INTERSECT operator is used to output only records that are common to two queries, that is, records that are retrieved by both queries.

Example

<Query> Display a list of all items in the goods table that have been ordered at least once.

SELECT gno FROM goods 
INTERSECT 
SELECT gno FROM orders;

GNO                   
------------------------
A111100002                                                    
E111100001                                                    
D111100008                                                    
D111100004                                                    
C111100001                                                    
E111100002                                                    
D111100002                                                    
D111100011                                                    
D111100003                                                    
D111100010                                                    
E111100012                                                    
F111100001                                                    
E111100009                                                    
E111100010                                                    
E111100007                                                    
E111100013                                                    
16 rows selected.

MINUS

Syntax

SELECT statement1 MINUS SELECT statement2

Description

The MINUS operator is used to output the result of the first search except the second.

Example

<Query> Display the product number of all products that have never been ordered.

SELECT gno FROM goods
MINUS
SELECT gno FROM orders;

GNO                   
------------------------
A111100001            
B111100001            
C111100002            
D111100001            
D111100005            
D111100006            
D111100007            
D111100009            
E111100003            
E111100004            
E111100005            
E111100006            
E111100008            
E111100011            
14 rows selected.

Order of Operations

The Order of Operations, also known as Operator Precedence, is the order in which the database evaluates the different operators in an expression. When an expression containing multiple operators is evaluated, operators having higher precedence are evaluated before those having lower precedence. Operators having equal precedence are evaluated in the order in which they appear in an expression, i.e. from left to right.

Description

The SQL operators are listed in the following table in decreasing order of operator precedence. Parentheses can be used within an expression to override operator precedence.

Precedence Level	Operator
1	All comparison operators
2	NOT
3	AND
4	OR

Examples

<Query> Display the name, position, and wage of engineers who earn more than 1850 dollars per month, as well as the name, position, and wage of all salespersons, regardless of their salary.

SELECT e_firstname
     , e_lastname
     , emp_job
     , salary
  FROM employees
 WHERE emp_job = 'sales rep'
    OR emp_job = 'engineer'
   AND salary >= 1850;

E_FIRSTNAME           E_LASTNAME            EMP_JOB          SALARY
------------------------------------------------------------------------------
Ken                   Kobain                engineer         2000
Sandra                Hammond               sales rep        1890
Alvar                 Marquez               sales rep        1800
William               Blake                 sales rep
4 rows selected.

<Query> Display the name, position, and wage of all salespersons who earn more than 1850 dollars per month and all engineers who earn more than 1850 dollars per month.

SELECT e_firstname
     , e_lastname
     , emp_job
     , salary
  FROM employees
 WHERE (emp_job = 'sales rep' OR emp_job = 'engineer')
   AND salary >= 1850;

E_FIRSTNAME           E_LASTNAME            EMP_JOB          SALARY
------------------------------------------------------------------------------
Ken                   Kobain                engineer         2000
Sandra                Hammond               sales rep        1890
2 rows selected.