How to use SQL WITH clause with explanation & example

The SQL WITH clause is very similar to the use of global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. therefore, SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring).
The SQL WITH clause is basically a drop-in replacement to the normal sub-query, the name assigned to the sub-query is treated as though it was an inline view or table.

There are some following notes about WITH clause:
The SQL WITH clause only works on the following version such as Oracle 9i release 2 and beyond
The SQL WITH clause is also used when we need a subquery will be executed multiple times.
therefore, the WITH clause is called subquery factoring.
it is also useful for recursive queries such as (SQL-99, but not Oracle SQL)
The clause is also called as (CTE) common table expression.
it is not supported by all database systems.

Syntax:

WITH query_name (column_name1, ...) AS
     (SELECT ...)
     
SELECT ...

WITH query_name (column_name1, ...) AS

(SELECT ...)

SELECT ...

here in general syntax, I have used a syntax after the keyword WITH is the same as it is for creating a view, therefore it will be started with query name after that in the parenthesis columns names, it returns.
these keywords introduced the definition itself after that query started again in the parentheses.

therefore, here With is not an alone command like create view is: it must be followed by select. It contains query and subqueries and therefore just need to defined query name in there from clause.

here, you can introduce a single clause with multiple query names by separating them with a comma, one thing notice here the with keyword is not repeated. therefore, Each query can refer to the query names.

WITH query_name1 AS (
     SELECT ...
     )
   , query_name2 AS (
     SELECT ...
       FROM query_name1
        ...
     )
SELECT ...

WITH query_name1 AS (

SELECT ...

)

, query_name2 AS (

SELECT ...

FROM query_name1

...

)

SELECT ...

there is the following syntax of the SQL WITH clause when using a single sub-query alias.

WITH <alias_name> AS (sql_subquery_statement)
SELECT column_list FROM <alias_name>[,table_name]
[WHERE <join_condition>]

WITH <alias_name> AS (sql_subquery_statement)

SELECT column_list FROM <alias_name>[,table_name]

[WHERE <join_condition>]

When using multiple sub-query aliases, the syntax is as follows.

WITH <alias_name_A> AS (sql_subquery_statement),
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )
SELECT <column_list>
FROM <alias_name_A>, <alias_name_B> [,table_names]
[WHERE <join_condition>]

WITH <alias_name_A> AS (sql_subquery_statement),

<alias_name_B> AS(sql_subquery_statement_from_alias_name_A

or sql_subquery_statement )

SELECT <column_list>

FROM <alias_name_A>, <alias_name_B> [,table_names]

[WHERE <join_condition>]

In the above-given syntax, the occurrence of alias_name is a meaning that you would give to the sub-query after the AS clause.

Each sub-query should be separated with a comma such as an Example for WITH statement. The rest of the queries will follow the rules standard formats for simple and complex SQL SELECT queries.

Example:1

Question: Find all the teachers whose salary is more than the average salary of all teachers.
Name of the relation: teachers

TEACHER_ID	TEACHER_NAME	SUBJECT	SALARY
145	Mark Devid	Mathematics	60000
151	Jacob Mahtab	English	50000
142	Lory Bryed	Physics	70000
155	Simans Gill	Geography	40000

WITH temporaryTable(averageValue) as
(SELECT avg(Salary)
from teachers), 
SELECT TEACHER_ID,TEACHER_NAME,SUBJECT,SALARY 
FROM teachers, temporaryTable 
WHERE teachers.Salary > temporaryTable.averageValue;

WITH temporaryTable(averageValue) as

(SELECT avg(Salary)

from teachers),

SELECT TEACHER_ID,TEACHER_NAME,SUBJECT,SALARY

FROM teachers, temporaryTable

WHERE teachers.Salary > temporaryTable.averageValue;

TEACHER_ID	TEACHER_NAME	SUBJECT	SALARY
145	Mark Devid	Mathematics	60000
142	Lory Bryed	Physics	70000

Explanation:

the average salary of all teachers is 55000. thus we can say that all teachers whose salary is more than the obtained average lie in the output relation.

Example:2

Find all the car Brands where the total cost of all the car in the car brands is more than the average of the total cost of all car models in the database.
Name of the relation: car models

manufacturing_unit	carbrands	model	totalcost
11211	Maruti Suzuki	Desizre	650000
11212	Ford	Classic	800000
11213	Nissan	DLX	850000
11214	Toyota	Glanza	700000
11215	Hyundai	Elitei20	600000

WITH totalcost(carbrands, total) as
    (SELECT carbrands, sum(cost)
    FROM model
    GROUP BY carbrands),
    carbrandsAverage(avgcost) as 
    (SELECT avg(cost)
    FROM model )
    SELECT carbrands
    FROM totalcost, carbrandsAverage
    WHERE totalcost.total > carbrandsAverage.avgcost;

WITH totalcost(carbrands, total) as

(SELECT carbrands, sum(cost)

FROM model

GROUP BY carbrands),

carbrandsAverage(avgcost) as

(SELECT avg(cost)

FROM model )

SELECT carbrands

FROM totalcost, carbrandsAverage

WHERE totalcost.total > carbrandsAverage.avgcost;

manufacturing_unit	carbrands	model	totalcost
11212	Ford	Classic	800000
11213	Nissan	DLX	850000

Important Notice:

Basically SQL WITH clause is a drop-in-replacement to the normal sub-query.
The SQL WITH clause used with complex SQL statements rather than simple ones.
SQL WITH clause allows to you break down complex SQL queries into smaller ones, it makes it easy for debugging and processing complex queries.

How to use SQL WITH clause with explanation & example

Syntax:

there is the following syntax of the SQL WITH clause when using a single sub-query alias.

Example:1

Explanation:

Example:2

Important Notice:

Related Posts

About Author

admin