How to Use the SQL WITH Clause

How to Use the SQL WITH Clause
Page content

Introduction

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 to be referenced in order to reduce the complexity of SQL statements. This process is also referred to as 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. for additional information and tips on SQL, take a look at our SQL Coding Tips page.

Syntax For The SQL WITH Clause

The following is the syntax of the SQL WITH clause when using a single subquery alias.

WITH <alias_name> AS (sql_subquery_statement)

SELECT column_list FROM <alias_name>[,tablename]

[WHERE <join_condition>]

When using multiple subquery aliases, the sysntax 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>, [tablenames]

[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. The rest of the queries follow the standard formats for simple and complex SQL SELECT queries.

Examples Using The SQL WITH Clause

Using the Single alias as described above you can play around with your queries in the following manner. Take this SQL query for instance.

SELECT foo FROM bar;

The result being:

Foo- - - X1 row selected

Here is the same query using the SQL WITH clause.

WITH alias AS (SELECT foo FROM bar)SELECT * FROM alias

The result being:

Foo- - - X1 row selected

The result is exactly the same so therefore we can see that the alias is actually an name “alias” represents the query “SELECT foo FROM bar”. Here now is an example of how you can use multiple WITH clauses within your SQL query.

WITH alias_1 AS (SELECT foo1 c FROM bar)

, alias_2 AS ( SELECT foo2 c FROM bar a, alias_1 b WHERE b.c = a.c )

SELECT * FROM alias_2 a

The result in this case is as follows:

c- - - X1 row selected.

As in the example above, you can use any number of WITH clauses as you would need within your queries. The same example above shows that you can even have complex SQL query statements used as sub-queries within the WITH clauses. As you will see, not only can the WITH clause be used with SELECT statements, it can also be used in other statements such as INSERT, DELETE, ALTER and basically where any other SQL statements can be used. The following example uses the SQL WITH clause inside an INSERT statement.

INSERT INTO new_table

SELECT * FROM (

WITH alias_name AS (

SELECT a, b, c………… FROM ole_table)

SELECT * FROM alias_name)

The query above will end up populating your new table with data pulled from the old table.

Conclusion

The SQL WITH clause is ideal in using with complex SQL statements rather than simple ones in that it allows you to create SQL queries that are friendly to read. It also allows you break down complex SQL queries into bite sized chunks that make it easy for debugging and processing the complex queries. Other SQL functions worth looking at include the SQL Server DateAdd function for adding and subtracting dates as well as the SQL Count function for doing totals.