SQL WITH clause example [duplicate]

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 Posts:

  1. What is the difference between UNION and UNION ALL?
  2. Selecting COUNT(*) with DISTINCT
  3. ORA-00904: invalid identifier
  4. The wait operation timed out. ASP
  5. Conversion failed when converting date and/or time from character string while inserting datetime
  6. When should I use CROSS APPLY over INNER JOIN?
  7. Insert Data Into Temp Table with Query
  8. error, string or binary data would be truncated when trying to insert
  9. SQL SELECT WHERE field contains words
  10. Why do we need “Relationships” between tables at all?
  11. Using group by on multiple columns
  12. How to declare a variable in MySQL?
  13. ORA-01843 not a valid month- Comparing Dates
  14. How do I escape a single quote in SQL Server?
  15. How do you trouble shoot a “Data type mismatch in criteria expression” error in MS Access 2010?
  16. Inserting data into a temporary table
  17. How to Select Top 100 rows in Oracle?
  18. CREATE VIEW must be the only statement in the batch
  19. SQL Server FOR EACH Loop
  20. How can I select the first day of a month in SQL?
  21. Warning: Null value is eliminated by an aggregate or other SET operation in Aqua Data Studio
  22. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  23. Oracle Apex column link from LOV values
  24. When to use “ON UPDATE CASCADE”
  25. MySQL combine two columns into one column
  26. ORA-01861: literal does not match format string
  27. SQL Server Insert if not exists
  28. SQL Server: Invalid Column Name
  29. How do I do multiple CASE WHEN conditions using SQL Server 2008?
  30. Insert text with single quotes in PostgreSQL
  31. SQL Server IF NOT EXISTS Usage?
  32. String or binary data would be truncated. The statement has been terminated
  33. How to insert date values into table
  34. Error converting data type varchar to float
  35. How to concatenate text from multiple rows into a single text string in SQL Server
  36. Count(*) vs Count(1) – SQL Server
  37. Arithmetic overflow error converting numeric to data type numeric
  38. Remove duplicate rows in MySQL
  39. I want to use CASE statement to update some records in sql server 2005
  40. how to remove time from datetime
  41. “You tried to execute a query that does not include the specified aggregate function”
  42. How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?
  43. How to round down to nearest integer in MySQL?
  44. How do I reset a sequence in Oracle?
  45. Query to convert from datetime to date mysql
  46. MySQL error: key specification without a key length
  47. SQL: Two select statements in one query
  48. How to query MongoDB with “like”
  49. Check if MySQL table exists without using “select from” syntax?
  50. MySQL equivalent of DECODE function in Oracle
  51. mysql update column with value from another table
  52. what is the difference between triggers, assertions and checks (in database)
  53. Replacing NULL with 0 in a SQL server query
  54. SQL Server dynamic PIVOT query?
  55. How to delete from multiple tables in MySQL?
  56. How to copy a row and insert in same table with a autoincrement field in MySQL?
  57. Backup a single table with its data from a database in sql server 2008
  58. Error converting data type varchar
  59. I want to use CASE statement to update some records in sql server 2005
  60. What does Include() do in LINQ?
  61. SQL Server “cannot perform an aggregate function on an expression containing an aggregate or a subquery”, but Sybase can
  62. Get everything after and before certain character in SQL Server
  63. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  64. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  65. ROW_NUMBER() in MySQ
  66. LEFT function in Oracle
  67. Foreign key references invalid table
  68. What are database constraints?
  69. Postgresql column reference “id” is ambiguous
  70. Inner Joining three tables
  71. How do I view the Explain Plan in Oracle Sql developer?
  72. ORA-01779: cannot modify a column which maps to a non key-preserved table
  73. SUM OVER PARTITION BY
  74. SQLite string contains other string query
  75. Varchar invalid for Sum operator
  76. SQL: How to properly check if a record exists
  77. Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF
  78. Sorting search results by taxonomy terms
  79. SQL select of users by metadata
  80. How to properly sanitize strings without $wpdb->prepare?
  81. WordPress SQL LIKE request doesn’t work for fields with special symbols
  82. posts_where Fails with More than One Custom Field in Query
  83. WordPress database error: You have an error in your SQL syntax
  84. Restose content from revisions – sql query
  85. Better way to migrate to server?
  86. Inner Join user tables to select users with roles
  87. $wpdb->prepare with LIKE returning blank array instead of rows
  88. Removing posts by sql
  89. Why line returns are not reapply after doing esc_sql?
  90. WooCommerce database query : Get product category image path+name from thumbnail_id [closed]
  91. WordPress can I manually add columns in users table and display it in the default manage user plugin and registration form? [closed]
  92. Help me SELECT thumbnail from SQL and use
  93. post id not displaying
  94. Why is my insert row only inserting the final row from the loop into the database rather than just inserting one
  95. Search results sort order failing: set by date only
  96. Insert multiple checkbox values
  97. Creating a “forum” – showing last post or last commented post
  98. Replace Unwanted Space in Post Content URL
  99. SQL to transform all email addresses in my DB in lowercase [closed]
  100. How to write inner join using posts_clauses?

Leave a Comment