Pin Me

SQL's WITH Clause: Explanation & Examples

written by: S. R. Obbayi•edited by: Amy Carson•updated: 3/11/2011

The SQL WITH clause may not be familiar to most database administrators. This is because the WITH clause is most likely to be found by those using Oracle 9i release 2. So what exactly does this clause do? This tutorial explains it all.

  • slide 1 of 4

    Introduction

    schema 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.

  • slide 2 of 4

    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.

  • slide 3 of 4

    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.

  • slide 4 of 4

    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.