SQL WITH clause example

Question

I was trying to understand how to use the WITH clause and the purpose of the WITH clause.

All I understood was, the WITH clause was a replacement for normal sub-queries.

Can anyone explain this to me with a small example in detail ?

http://msdn.microsoft.com/en-us/library/ms175972(v=sql.105).aspx — Vikdor, Sep 23 '12 at 12:17
I put together an example how to use the WITH clause to create temporary static table. You might find this useful, since it contains some examples on how to use WITH clauses in your queries. http://www.giombetti.com/2014/02/14/using-sql-with-clause-to-create-temporary-static-tables-at-query-time/ — Marc Giombetti, Feb 14 '14 at 12:42
since the OP made the post, MySQL implemented the WITH statement: https://dev.mysql.com/doc/refman/8.0/en/with.html — Pierre C., Aug 28 '19 at 13:18
Very inappropriate to mark as duplicate in my opinion, especially given that from an entry level, no-one would ever click on the question, and secondly the answer is wildly different. And this one is more practically useful... — AER, Sep 19 '19 at 07:02

score 348 · Answer 1 · edited Jan 18 '18 at 02:39

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query. The name assigned to the sub-query is treated as though it was an inline view or table. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.

Syntax For The SQL WITH Clause

The following is the 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>]

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>]

In the syntax documentation above, the occurrences of alias_name is a meaningful name you would give to the sub-query after the AS clause. Each sub-query should be separated with a comma Example for WITH statement. The rest of the queries follow the standard formats for simple and complex SQL SELECT queries.

For more information: http://www.brighthub.com/internet/web-development/articles/91893.aspx

:I've already seen it,can you explain this with a small example which would be more easy to under stand. — , Sep 24 '12 at 03:37
I think you need a comma after the WITH ` AS (sql_subquery_statement)` when using multiple subquery aliases. — Jason, Aug 23 '13 at 18:29

score 118 · Answer 2 · edited May 23 '17 at 12:34

118

This has been fully answered here.

See Oracle's docs on SELECT to see how subquery factoring works, and Mark's example:

WITH employee AS (SELECT * FROM Employees)
SELECT * FROM employee WHERE ID < 20
UNION ALL
SELECT * FROM employee WHERE Sex = 'M'

edited May 23 '17 at 12:34

Community

1
1

answered Sep 23 '12 at 12:23

Lorenz Lo Sauer

23,698
16
85
87

2

Thank you especially for the first sentence with a link :) – Lukasz Czerwinski Jul 08 '13 at 19:57
8

Yeah, basically it replaces a subquery BUT allows you to reference the "subquery" result in multiple places. – kiradotee Feb 20 '18 at 14:17
Shouldn't you mention the attributes in the in the parenthesis instead of (SELECT * FROM Employees)? For example, WITH employee AS (Name, ID). – Agent 0 Oct 22 '18 at 23:25
1

In MSSQL, you need to add a semicolon (;) before WITH, order wise you will get an error. it should be `;WITH blabla AS ...)` – Obinna Nnenanya Dec 06 '18 at 17:59

SQL WITH clause example

2 Answers2

Linked