What is the meaning of query 1

With - structure complex queries

In software development, it is common to group instructions into small, easy-to-understand units - e. B. in functions or methods. This makes the corresponding part of the program reusable and easier to read.

Although SQL also has functions and procedures, they are not the right tool for creating easily understandable, reusable entities. In SQL, neither functions nor procedures are first class citizens the way subqueries are.0 The basic building block of SQL are queries1 - not instructions.

To make queries reusable, there have been views since SQL-92. As soon as a view has been created, it has a name in the database schema and can be used in queries like a table.

In SQL: 1999 the clause “Views limited to a statement” were introduced: they are not saved, but only apply in the query in which they are defined. This allows you to improve the structure of an instruction without burdening the global namespace ("namespace pollution").

The clause is also under the name Common Table Expression (CTE) and Sub-query factoring known. The form of the clause is covered in another article.

syntax

Put simply, the -clause is an optional prefix for 2:

After the keyword, the syntax corresponds to that of: it begins with the query name followed by an optional list3 that assigns names to the result columns. This is followed by the keyword that introduces the actual definition (i.e. the query).

In contrast to is not an independent instruction: must follow immediately. This query (and its subqueries) can use the query name just defined in the clause.

A single clause can define multiple query names separated by commas - the keyword is not repeated. Each of these queries can already access query names that were previously defined in the same clause (see compatibility): 4

Query names that are also defined hide tables and views of the same name. 5

Performance aspects

Most databases treat queries in the same way as views: they replace the reference to a query with the definition and then optimize the query as a whole.

The PostgreSQL database was different in this regard prior to version 12: it optimized each query and the main statement independently.

If a query is referenced more than once in a query, some databases cache the result to prevent the query from being executed multiple times.

More on this in "WITH clause: performance impact“.

use cases

compatibility

The clause was introduced in SQL: 1999 as a collection of optional functions. Disregarding the variant, these are T121 for top-level clauses and T122 for subquery clauses.

The basic function of is largely supported. The only area where the products differ is in name resolution. It is particularly noticeable that it is often treated as if.6 Slightly more subtle deviations from the standard concern qualified table names (cannot denote an -query7) and views that are used in the visibility area of ​​an -query (the query in the view cannot "see" the outside 8 ).

Compliant alternatives

Some use cases can also be implemented with views. However, this can quickly lead to a disproportionate number of views ("namespace pollution"). Therefore, in these cases, using subqueries is often a better option.

Proprietary extensions

as DML prefix (PostgreSQL, SQL Server, SQLite)

Some databases accept DML statements as a prefix (Docu: PostgreSQL, SQL Server, SQLite).

With SQL Server, a query can also be the target of the DML statement (similar to an updatable view).

Functions in (Oracle)

From version 12cR1 the Oracle database supports function and procedure declaration in the clause (documentation).

DML in (PostgreSQL)

As of version 9.1, the PostgreSQL database supports DML statements (,,) in the -clause (documentation). If you then use the (also proprietary) clause of the DML statement, the clause also returns a result - e.g. B. the inserted lines.