WITH Clause in SELECT statement

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 WITH clause, 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)  

Thanks