WITH Clause in SELECT statement

• Using the WITH clause, you can use the same query block in a SELECT statement when it occurs

more than once within a complex query.

• The WITH clause retrieves the results of a query block and stores it in the user's temporary tablespace.
• The WITH clause improves performance

WITH
dept_costs AS (
  SELECT d.department_name, SUM(e.salary) AS dept_total
  FROM employees e, departments d
  WHERE e.department_id = d.department_id
  GROUP BY d.department_name),
avg_cost AS (
   SELECT SUM(dept_total)/COUNT(*) AS dept_avg
   FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;


I will explain best practice and benefits of using WITH clause in Oracle Database.

WITH is used with SELECT query to collect the data set first and then query against collected data set in WITHclause, there for the query doesn't start with SELECT, it will start with WITH clause first.

Syntax of WITH clause

WITH with_clause_name AS ( SELECT STATEMENT)

SELECT *

  FROM with_clause_name;

Example

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name;  

From previous example the WITH clause allow you to give name to SELECT statement and then later select from this named SELECT statement.

Benefits of WITH clause

1- We can reference a named SELECT any number of times
For example 

 WITH name_statement AS (SELECT 1 one FROM DUAL)  
 SELECT t1.one, t2.one  
  FROM name_statement t1, name_statement t2;  

2- We can create any number of named query statements
For example

 WITH name_statement1 AS (SELECT 1 one FROM DUAL),  
    name_statement2 AS (SELECT 2 two FROM DUAL)  
 SELECT one, two  
  FROM name_statement1, name_statement2;  

3-Named query statement can reference any other name query statement that came before it and can be correlated also.
For example

 WITH name_statement1 AS (SELECT 1 one FROM DUAL),  
    name_statement2 AS (SELECT 2 two  
               FROM DUAL, name_statement1  
              WHERE name_statement1.one = 1)  
 SELECT one, two  
  FROM name_statement1, name_statement2;  

4- Named query statement are valid on its scope only, so you can't select from it from another place.

If we execute the below statement

WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name;  

and try to select from named query statement without using WITH clause 

SELECT *  
  FROM with_clause_name; 

It will raise an error  "ORA-00942: table or view does not exist"

5- Reusable of named query statement.

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT * FROM with_clause_name  
 UNION ALL  
 SELECT * FROM with_clause_name;  

6- We can list after FROM named query, table, view and synonym.

 WITH with_clause_name AS (SELECT 1 one FROM DUAL)  
 SELECT *  
  FROM with_clause_name, emp;  

7- We can use named query statement in INSERT, UPDATE statements

 INSERT INTO EMP (EMPNO)  
   SELECT *  
    FROM (WITH WITH_CLAUSE_NAME AS (SELECT 1 ONE FROM DUAL)  
       SELECT *  
        FROM WITH_CLAUSE_NAME)