SQL WITH clause example

sql with-clause
user1213167 picture user1213167 · Sep 23, 2012 · Viewed 947.3k times · Source

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 ?

Answer

cc4re picture cc4re · Sep 23, 2012

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

Related questions

SQL Server - using the WITH clause in an INSERT statement

I was wondering if this was possible. I have an existing query that uses the WITH clause to apply some aggregated data to a SELECT query like so: (massively simplified) ;WITH alias (y,z) AS ( SELECT y,z FROM tableb ) …

Read More
Postgres "missing FROM-clause entry" error on query with WITH clause

I am trying to use this query in Postgres 9.1.3: WITH stops AS ( SELECT citation_id, rank() OVER (ORDER BY offense_timestamp, defendant_dl, offense_street_number, offense_street_name) AS stop FROM consistent.master WHERE citing_jurisdiction=1 ) UPDATE consistent.master …

Read More
How to create a table using "With" clause in SQL

I'm attempting to create a persistent table using the WITH clause however, I'm getting an error. For context the answer that I currently find is CREATE TABLE my_table AS WITH my_tables_data AS ( SELECT another_table.data1 AS …

Read More