Set operators are used to join the results of two (or more) SELECT statements.The SET operators available in Oracle 11g are UNION,UNION ALL,INTERSECT,and MINUS.

The UNION set operator returns the combined results of the two SELECT statements.Essentially,it removes duplicates from the results i.e. only one row will be listed for each duplicated result.To counter this behavior,use the UNION ALL set operator which retains the duplicates in the final result.INTERSECT lists only records that are common to both the SELECT queries; the MINUS set operator removes the second query”s results from the output if they are also found in the first query”s results. INTERSECT and MINUS set operations produce unduplicated results.

All the SET operators share the same degree of precedence among them.Instead,during query execution, Oracle starts evaluation from left to right or from top to bottom.If explicitly parentheses are used, then the order may differ as parentheses would be given priority over dangling operators.

Points to remember –

• Same number of columns must be selected by all participating SELECT statements.Column names used in the display are taken from the first query.

• Data types of the column list must be compatible/implicitly convertible by oracle. Oracle will not perform implicit type conversion if corresponding columns in the component queries belong to different data type groups.For example, if a column in the first component query is of data type DATE, and the corresponding column in the second component query is of data type CHAR,Oracle will not perform implicit conversion, but raise ORA-01790 error.

• Positional ordering must be used to sort the result set. Individual result set ordering is not allowed with Set operators. ORDER BY can appear once at the end of the query. For example,

• UNION and INTERSECT operators are commutative, i.e. the order of queries is not important; it doesn”t change the final result.

• Performance wise, UNION ALL shows better performance as compared to UNION because resources are not wasted in filtering duplicates and sorting the result set.

• Set operators can be the part of sub queries.

• Set operators can”t be used in SELECT statements containing TABLE collection expressions.

• The LONG, BLOB, CLOB, BFILE, VARRAY,or nested table are not permitted for use in Set operators.For update clause is not allowed with the set operators.

UNION

When multiple SELECT queries are joined using UNION operator, Oracle displays the combined result from all the compounded SELECT queries,after removing all duplicates and in sorted order (ascending by default), without ignoring the NULL values.

Consider the below five queries joined using UNION operator.The final combined result set contains value from all the SQLs. Note the duplication removal and sorting of data.

SELECT 1 NUM FROM DUAL
UNION
SELECT 5 FROM DUAL
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;

NUM
-------
1
3
5
6

To be noted, the columns selected in the SELECT queries must be of compatible data type. Oracle throws an error message when the rule is violated.

SELECT TO_DATE(''12-OCT-03'') FROM DUAL
UNION
SELECT ''13-OCT-03'' FROM DUAL;

SELECT TO_DATE(''12-OCT-03'') FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

UNION ALL

UNION and UNION ALL are similar in their functioning with a slight difference. But UNION ALL gives the result set without removing duplication and sorting the data. For example,in above query UNION is replaced by UNION ALL to see the effect.

Consider the query demonstrated in UNION section. Note the difference in the output which is generated without sorting and deduplication.

SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

NUM
-------
1
5
3
6
3

INTERSECT

Using INTERSECT operator, Oracle displays the common rows from both the SELECT statements, with no duplicates and data arranged in sorted order (ascending by default).

For example,the below SELECT query retrieves the salary which are common in department 10 and 20.As per ISO SQL Standards, INTERSECT is above others in precedence of evaluation of set operators but this is not still incorporated by Oracle.

SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 20

SALARY
---------
1500
1200
2000

MINUS

Minus operator displays the rows which are present in the first query but absent in the second query, with no duplicates and data arranged in ascending order by default.

SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;

JOB_ID
-------------
HR
FIN
ADMIN

Matching the SELECT statement

There may be the scenarios where the compound SELECT statements may have different count and data type of selected columns. Therefore, to match the column list explicitly, NULL columns are inserted at the missing positions so as match the count and data type of selected columns in each SELECT statement. For number columns, zero can also be substituted to match the type of the columns selected in the query.

In the below query, the data type of employee name (varchar2) and location id (number) do not match. Therefore, execution of the below query would raise error due to compatibility issue.

SELECT DEPARTMENT_ID "Dept", first_name "Employee"
FROM employees
UNION
SELECT DEPARTMENT_ID, LOCATION_ID
FROM departments;

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

Explicitly, columns can be matched by substituting NULL for location id and Employee name.

SELECT DEPARTMENT_ID "Dept", first_name "Employee", NULL "Location"
FROM employees
UNION
SELECT DEPARTMENT_ID, NULL "Employee", LOCATION_ID
FROM departments;

Using ORDER BY clause in SET operations

The ORDER BY clause can appear only once at the end of the query containing compound SELECT statements.It implies that individual SELECT statements cannot have ORDER BY clause. Additionally, the sorting can be based on the columns which appear in the first SELECT query only. For this reason, it is recommended to sort the compound query using column positions.

The compund query below unifies the results from two departments and sorts by the SALARY column.

SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=10
UNION
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=20
ORDER BY 3;

Retrieving data using the SQL Select Statement

SQL is a comprehensive database language. SQL, pronounced Sequel or simply S-Q-L, is a computer programming language used for querying relational databases following a nonprocedural approach. When you extract information from a database using SQL, this is termed querying the database.

A relational database is implemented through the use of a Relational Database Management System (RDBMS). An RDBMS performs all the basic functions of the DBMS software mentioned above along with a multitude of other functions that make the relational model easier to understand and to implement. RDBMS users manipulate data through the use of a special data manipulation language. Database structures are defined through the use of a data definition language. The commands that system users execute in order to store and retrieve data can be entered at a terminal with an RDBMS interface by typing the commands, or entered through use of some type of graphical interface. The DBMS then processes the commands.

Capabilities of the SELECT Statement

Data retrieval from data base is done through appropriate and efficient use of SQL. Three concepts from relational theory encompass the capability of the SELECT statement: projection, selection, and joining.

• Projection: A project operation selects only certain columns (fields) from a table. The result table has a subset of the available columns and can include anything from a single column to all available columns.

• Selection: A select operation selects a subset of rows (records) in a table (relation) that satisfy a selection condition. The ability to select rows from out of complete result set is called Selection. It involves conditional filtering and data staging. The subset can range from no rows, if none of the rows satisfy the selection condition, to all rows in a table.

• Joining: A join operation combines data from two or more tables based on one or more common column values. A join operation enables an information system user to process the relationships that exist between tables. The join operation is very powerful because it allows system users to investigate relationships among data elements that might not be anticipated at the time that a database is designed.

Consider the above table structures. Fetching first_name name, department_id and salary for a single employee from EMPLOYEES table is Projection. Fetching employee details whose salary is less than 5000, from EMPLOYEES table is Selection. Fetching employee”s first name, department name by joining EMPLOYEES and DEPARTMENTS is Joining.

Basic SELECT statement

The basic syntax for a SELECT statement is presented below.

SELECT  [DISTINCT | ALL] {* | select_list}
FROM {table_name [alias] | view_name}
    [{table_name [alias]  | view_name}]...
[WHERE  condition]
[GROUP BY  condition_list]
[HAVING  condition]
[ORDER BY  {column_name | column_#  [ ASC | DESC ] } ...

The SELECT clause is mandatory and carries out the relational project operation.

The FROM clause is also mandatory. It identifies one or more tables and/or views from which to retrieve the column data displayed in a result table.

The WHERE clause is optional and carries out the relational select operation. It specifies which rows are to be selected.

The GROUP BY clause is optional. It organizes data into groups by one or more column names listed in the SELECT clause.

The optional HAVING clause sets conditions regarding which groups to include in a result table. The groups are specified by the GROUP BY clause.

The ORDER BY clause is optional. It sorts query results by one or more columns in ascending or descending order.

Arithmetic expressions and NULL values in the SELECT statement

An arithmetic expression can be created using the column names, operators and constant values to embed an expression in a SELECT statement. The operator applicable to a column depends on column”s data type. For example, arithmetic operators will not fit for character literal values. For example,

SELECT employee_id, sal * 12 ANNUAL_SAL
FROM employees;

The above query contains the arithmetic expression (sal * 12) to calculate annual salary of each employee.

Arithmetic operators

Operators act upon the columns (known as operands) to result into a different result. In case of multiple operators in an expression, the order of evaulation is decided by the operator precedence. Here are the elementary rules of precedence –

• Multiplication and division occur before Addition and Subtraction.

• Operators on the same priority are evaluated from left to right.

• Use paretheses to override the default behavior of the operators.

Below table shows the precedence of the operators, in such cases. Precedence Level Operator Symbol Operation

Description   Operator	Precedence
Addition	+	Lowest
Subtraction	-	Lowest
Multiplication	*	Medium
Division	/	Medium
Brackets	( )	Highest

Examine the below queries (a), (b), and (c)

• SQL> SELECT 2*35 FROM DUAL;

• SQL> SELECT salary + 1500 FROM employees;

• SQL> SELECT first_name, salary, salary + (commission_pct* salary) FROM employees;

Query (a) multiplies two numbers, while (b) shows addition of $1500 to salaries of all employees. Query (c) shows the addition of commission component to employee”s salary. As per the precedence, first commission would be calculated on the salary, and then added to the salary.

Column Alias

An alias is used to rename a column or an expression during display. The alias to a column or an expression appears as the heading in the output of a query. It is useful in providing a meaningful heading to long expressions in the SELECT query. By default, the alias appears in uppercase in the query output without spaces. To override this behavior, alias must be enclosed within double quotes to preserve the case and spaces in the alias name.

SELECT price * 2 as DOUBLE_PRICE, price * 10 "Double Price"
FROM products;

DOUBLE_PRICE	Double Price
------------	------------
39.9			39.9
60			60
51.98			51.98

Concatenation operators

Concatenation operator can be used to join two string values or expressions in a SELECT query. The double vertical bar symbol is used as string concatenation operator. It is applicable only for character and string column values resulting into a new character expression. Example

SQL> SELECT ''ORACLE''||'' CERTIFICATION'' FROM dual;

The above query shows concatenation of two character literals values.

Literals

Any hard coded value, which is not stored in database, in the SELECT clause, is known s Literal. It can be number, character, or date value. Character and date values must be enclosed within quotes. Consider the below SQL queries.examples of using literals of different data types in SQL queries.

The query below uses two character literals to join them together.

SQL> SELECT ''ORACLE''||'' CERTIFICATION'' FROM DUAL

The query below uses character literals to pretty print the employee”s salary.

SQL> SELECT first_name ||''earns''|| salary||'' as of ''|||sysdate
FROM employees

Quote Operator

The quote operator is used to specify the quotation mark delimiter of your own. You can chose a convenient delimiter, depedning on the data.

SELECT 	department_name|| '' Department'' ||q''[''s Manager Id: ]''|| manager_id
FROM departments;

NULL

If a column doesn”t has a definite value, it is considered as NULL. NULL value denotes unknown or unavailable. It is not zero for numeric values, not blank space for character values.

Columns with NULL value can be selected in a SELECT query and can be the part of an arithmetic expression. Any arithmetic expression using NULL values results into NULL. For this reason, columns with NULL value must be handled differently by specifying their alternate values using Oracle supplied functions like NVL or NULLIF.

SQL> SELECT NULL + 1000 NUM
FROM DUAL;

NUM
--------

DISTINCT Keyword

If the data is expected to have duplicate results, use DISTINCT keyword to eliminate duplicates and diplay only the unique results in the query output. Only the selected columns are validated for duplication and the rows will be logically eliminated from the query output. To be noted, the DISTINCT keyword must appear just after the SELECT clause.

The simple query below demonstrates the use of DISTINCT to display unique department ids from EMPLOYEES table.

SQL> SELECT DISTINCT DEPARTMENT_ID
FROM employees;

DEPARTMENT_ID
---------------
10
20
30
40

DESCRIBE command

The structural metadata of a table may be obtained by querying the database for the list of columns that comprise it using the DESCRIBE command. It will list the used column names, their null property and data type.

Syntax:

DESC[RIBE] [SCHEMA].object name

For example,

DESC EMPLOYEE

will display the EMPLOYEE table structure i.e. columns, their data types, precision and nullable property.

A subquery is best defined as a query within a query. Subqueries enable you to write queries that select data rows for criteria that are actually developed while the query is executing at run time. More formally, it is the use of a SELECT statement inside one of the clauses of another SELECT statement. In fact, a subquery can be contained inside another subquery, which is inside another subquery, and so forth. A subquery can also be nested inside INSERT, UPDATE, and DELETE statements. Subqueries must be enclosed within parentheses.

A subquery can be used any place where an expression is allowed providing it returns a single value. This means that a subquery that returns a single value can also be listed as an object in a FROM clause listing. This is termed an inline view because when a subquery is used as part of a FROM clause, it is treated like a virtual table or view. Subquery can be placed either in FROM clause, WHERE clause or HAVING clause of the main query.

Oracle allows a maximum nesting of 255 subquery levels in a WHERE clause. There is no limit for nesting subqueries expressed in a FROM clause.In practice, the limit of 255 levels is not really a limit at all because it is rare to encounter subqueries nested beyond three or four levels.

A subquery SELECT statement is very similar to the SELECT statement used to begin a regular or outer query.The complete syntax of a subquery is:

( SELECT [DISTINCT] subquery_select_parameter
  FROM {table_name | view_name}
               {table_name | view_name} ...
  [WHERE search_conditions]
  [GROUP BY column_name [,column_name ] ...]
  [HAVING search_conditions] )

Types of Subqueries

Single Row Sub Query: Sub query which returns single row output. They mark the usage of single row comparison operators, when used in WHERE conditions.

Multiple row sub query: Sub query returning multiple row output. They make use of multiple row comparison operators like IN, ANY, ALL. There can be sub queries returning multiple columns also.

Correlated Sub Query: Correlated subqueries depend on data provided by the outer query.This type of subquery also includes subqueries that use the EXISTS operator to test the existence of data rows satisfying specified criteria.

Single Row Sub Query

A single-row subquery is used when the outer query”s results are based on a single, unknown value. Although this query type is formally called “single-row,” the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.

In the below SELECT query, inner SQL returns only one row i.e. the minimum salary for the company. It in turn uses this value to compare salary of all the employees and displays only those, whose salary is equal to minimum salary.

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary)
		FROM employees); 

A HAVING clause is used when the group results of a query need to be restricted based on some condition. If a subquery”s result must be compared with a group function, you must nest the inner query in the outer query”s HAVING clause.

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary)
			FROM employees)

Multiple Row Sub Query

Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows,it must be handled by set comparison operators (IN, ALL, ANY).While IN operator holds the same meaning as discussed in earlier chapter, ANY operator compares a specified value to each value returned by the sub query while ALL compares a value to every value returned by a sub query.

Below query shows the error when single row sub query returns multiple rows.

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id
			FROM employees
			WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row 

Usage of Multiple Row operators

• [> ALL] More than the highest value returned by the subquery

• [< ALL] Less than the lowest value returned by the subquery

• [< ANY] Less than the highest value returned by the subquery

• [> ANY] More than the lowest value returned by the subquery

• [= ANY] Equal to any value returned by the subquery (same as IN)

Above SQL can be rewritten using IN operator like below.

SELECT	first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                   	    FROM departments
                   	    WHERE LOCATION_ID = 100)

Note in the above query, IN matches department ids returned from the sub query,compares it with that in the main query and returns employee”s name who satisfy the condition.

A join would be better solution for above query, but for purpose of illustration, sub query has been used in it.

Correlated Sub Query

As opposed to a regular subquery, where the outer query depends on values provided by the inner query,a correlated subquery is one where the inner query depends on values provided by the outer query. This means that in a correlated subquery,the inner query is executed repeatedly, once for each row that might be selected by the outer query.

Correlated subqueries can produce result tables that answer complex management questions.

Consider the below SELECT query. Unlike the subqueries previously considered, the subquery in this SELECT statement cannot be resolved independently of the main query. Notice that the outer query specifies that rows are selected from the employee table with an alias name of e1. The inner query compares the employee department number column (DepartmentNumber) of the employee table with alias e2 to the same column for the alias table name e1.

SELECT EMPLOYEE_ID, salary, department_id
FROM   employees E
WHERE salary > (SELECT AVG(salary)
                FROM   EMP T
                WHERE E.department_id = T.department_id)

Multiple Column Sub Query

A multiple-column subquery returns more than one column to the outer query and can be listed in the outer query”s FROM, WHERE, or HAVING clause. For example, the below query shows the employee”s historical details for the ones whose current salary is in range of 1000 and 2000 and working in department 10 or 20.

SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
				  FROM employees
 				  WHERE salary BETWEEN 1000 and 2000
				  AND department_id BETWEEN 10 and 20)
ORDER BY first_name;

When a multiple-column subquery is used in the outer query”s FROM clause, it creates a temporary table that can be referenced by other clauses of the outer query. This temporary table is more formally called an inline view. The subquery”s results are treated like any other table in the FROM clause. If the temporary table contains grouped data, the grouped subsets are treated as separate rows of data in a table. Consider the FROM clause in the below query. The inline view formed by the subquery is the data source for the main query.

SELECT *
FROM (SELECT salary, department_id
	FROM employees
 	WHERE salary BETWEEN 1000 and 2000);