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.
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
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“.
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 ).
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.
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.
- What is your experience with FIRST Robotics
- Why should someone leave his friends behind?
- How many miles could a person walk?
- Do psychopaths get a headache
- Are there good and bad protein
- Who makes the real estate law
- What are the database credentials of a web application
- How does breastfeeding affect your body?
- How many shopping malls are there in Goa
- What is monitoring and evaluation about?
- Which problems can best be solved with microservices
- Why should I write poetry
- How does espresso taste
- How do crypto exchanges make money
- How do almonds increase hair growth
- How is life in Bangkok lately
- Why was Amazon created
- What is your rating of daPulse
- What are the problems with GST
- Who will buy personal loans
- How often do professional trumpeters practice
- Is vodka ethanol or methanol
- What are the types of shop window signs
- Who rejected the Nobel Peace Prize
- What are Small Business Startups in India
- What are the best professional scooters
- What is missing in current calendar applications
- How do crypto exchanges make money
- Who can get me a driver's license
- What is sex addiction
- Travel and vacation are the same
- Can you skate uphill?
- Why do pheromones work
- Are the people of Alabama friendly