Subqueries and it's Use in Oracle 10g

Scalar Subqueries:

Scalar Subquery return exactly one column value from one row. Multiple-column subqueries - pairwise or non-pairwise are not scalar. If a Scalar subquery return 0 rows, the value of the subquery expression is NULL. And if it returns more than one row, Oracle returns an error.

Valid Use of Scalar Subqueries:

• The condition and expression part of DECODE or CASE statement.
• All clauses of a SELECT statement except GROUP BY.
• The SET clause and the WHERE clause of an UPDATE statement.

e.g. 1

SELECT Employee_id, Last_name,

( CASE 

 WHEN department_id = 

( SELECT department_id FROM Departments

 WHERE location_id = 1800)

      THEN 'Canada' ELSE 'USA' END) LOCATION 

FROM EMPLOYEE;

e.g. 2

Sorting by a column that is not itself outputted - DEPARTMENT_NAME on the DEPARTMENTS table.

SELECT department_id, last_name

FROM Employees e

ORDER BY 

( SELECT department_name

FROM departments d

WHERE e.DEPARTMENT_ID = d.Department_id);

 

e.g.3

Query which should display Employee_Id, Department_Id and Salary column. It should also repeat the figure for the average company wide salary across a column caled COMPANY_AVG_SALARY:

SELECT employee_id, department_id, salary

(SELECT AVG(salary) FROM Employee) COMPANY_AVG_SALARY

FROM employees

ORDER BY department_id, salary, employee_id;

Co-related subquery:

A correlated subquery is the one that references a column from a table referred in the parent statement. Typically a subquery execute before main query. However a correlated subquery is evaluated once for each row processed by the parent statement. In other words, the inner query is driven by the outer query

Parent statement can be a SELECT, UPDATE or DELETE statement.

With Correlated Sub-queries: 

• outer query retrieves a candidate row.
• the inner query executes using one or more candidate row values.
• the output of the inner query qualifies or disqualifies the candidate row
• the process repeats until no row are left.

e.g 1: Find details of employees who earn more than the average salary for that department.

SELECT last_name, salary, department_id 

FROM employee outer 

WHERE salary >

 ( SELECT AVG(salary) 

   FROM employees

WHERE department_id = outer.employee_id);

e.g. 2: Display details for employees who have changed job at least twice. 

SELECT e.Employee_id, e.last_name, e.job_id

FROM employee e

WHERE 2 <= 

( SELECT COUNT(*) 

FROM job_history

WHERE employee_id = e.Employee_id);

EXITS:

In Subqueries we can also write EXISTS and NOT EXISTS operations. 

The EXISTS operator checks whether a value returned by the main query is present in the set of value returned by the subquery.  It is set to true if subquery returns one more more row otherwise it is set to FALSE.

NOT EXISTS work in opposite way. 

e.g.1 - Find employees who have at least one person reporting to them. 

SELECT employee_id, last_name, department_id

FROM employee outer

WHERE EXISTS 

(SELECT 'X' 

FROM Employees

WHERE manager_id = outer.employee_id);

Because the subquery is evaluated on whether it returns records, the X constant can be selected instead of an actual column.

e.g. 2 - Find Departments with no Employees:

SELECT department_id, department_name

FROM departments d

WHERE NOT EXISTS 

( SELECT 'X'

FROM employees 

WHERE department_id = d.department_id);

You have to be very careful when using the NOT IN construct as an alternative to NOT EXISTS operator. Thats because NOT IN evaluates to FALSE if any member of the required result set is a NULL value. So your query does not return any rows even if there are rows in the DEPARTMENTS table that satisfy the WHERE condition.

e.g.

SELECT department_id, department_name

FROM departments

WHERE department_id NOT IN ( SELECT department_id FROM EMPLOYEES);

NO ROW RETURNS

WITH Clause:

The WITH clause, whose formal name is subquery factoring clause, allows you to define a query block before putting it to use in a query.

Block results are stored in your temporary tablespace. They are reusable - so you dont have to invoke the same code repeatedly to retrieve the same information.

Advantages of WITH clause:

•  The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.
•  You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.
•  A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.
•  The SQL WITH clause only works on Oracle 9i release 2 and beyond. Formally, the WITH clause is called subquery factoring. The SQL WITH clause is used when a subquery is executed multiple times.
• Using WITH clause to create BLOCK can improve performance. It is helpful in complex queries that reference the same block several times or that use joins and aggregations. Code containing WITH clause is also easier to read.

Example: Provides detail of departments that have a higher total salary bill than the average department has.

WITH dept_costs AS (

SELECT d.department_name, SUM(e.salary) AS DEPT_TOT

FROM employees e, departments d

WHERE e.department_id = d.department_id

GROUP BY d.department_name),

  In this first WITH clause, you create  a block called dept_coste. It stores the department name and total salary spend for each department. You get the department_name value from the DEPARTMENTS table.

AVG_COST as (

SELECT AVG(dept_tot) AS DEPT_AVG

FROM dept_costs)

  In the second WITH clause, you create a block called AVG_COST. This stores one value: the average of the total salaries for every department.

  The average of the DEPT_TOT value in the DEPT_COSTS tablespace (which was created in the previous WITH clause) is placed in the DEPT_AVG alias). 

SELECT * 

FROM dept_costs

WHERE DEPT_TOT >

( SELECT DEPT_AVG

   FROM AVG_COST)

ORDER BY department_name;

 The main query returns a row for every department with a higher-than-average departmental total.

 It compares the total salary spend for each department - stored in the DEPT_TOT alias of the DEPT_COST block - with the average of all the total -stored in the DEPT_AVG alias of the AVG_COST block.

When we execute the query, a row is returned for the two departments with a higher-than-average total salary spend.