ORACLE with AS usage

Original Transmission gate: http://blog.csdn.net/wh62592855/archive/2009/11/06/4776631.aspx

Remember before in the forum to see Inthirties used with as the word, at that time did not particularly care. Today in a post and see someone use this, so go online search the relevant content, their small try a, write down, easy to forget the words after learning.

===================================================================================

Let me give you an example:

There are two tables, a, B, and the value of a field is first found in table A, if there is data in table A, the value of table A is output; if it does not exist in table A, then in table B, if there is a record in table B, the value of table B is output, and the "No records" string is output if the B table does not exist.

With SQL1 as (select To_char (a) s_name from Test_tempa), sql2 as (select To_char (b) s_name from TEST_TEMPB where not exist S (select S_name from SQL1 where rownum=1)) SELECT * To SQL1 UNION ALL SELECT * to SQL2 UNION ALL select ' No Records ' From dual where does exists (select S_name from SQL1 where rownum=1) and not EXISTS (select S_name from SQL2 where rownum=1 );

Let me give you a simple example.

With a as (SELECT * from Test)

SELECT * from A;

In fact, a lot of repeated SQL statements are placed in the with as inside, take an alias, the following query can use it

This is an optimization function for a large number of SQL statements, and it is clear

The following is a search of English documents

About Oracle with clause
Starting in Oracle9i Release 2 we are incorporation of the SQL-99 "with clause", a tool for materializing subqueries to Save Oracle from has to re-compute them the multiple times.

The SQL "with clause" are very similar to the use of Global temporary tables (GTT), a technique this is often used to Impro ve query speed for complex subqueries. Here is are some important notes about the Oracle "with clause":

the SQL "with clause" in Oracle 9i Release 2 and beyond.
formally, the ' with clause ' is called subquery factoring
the SQL "with clause" are used when a subquery are executed multiple times
also useful for recursive queries (SQL-99, but not Oracle SQL)

To keep it simple, the following example only references the aggregations once, where the SQL ' with clause ' is normally us Ed when a aggregation was referenced multiple times in a query.
We can also use the SQL-99 "with clause" instead of temporary tables. The Oracle SQL "with clause" would compute the aggregation once, give it a name, and allow us to reference it (maybe Multip Le Times), later in the query.

The SQL-99 "with clause" are very confusing at the A-because SQL statement does not begin with the word SELECT. Instead, we use the ' with clause ' to start our SQL query, defining the aggregations, which can then is named in the main Q Uery as if they were "real" tables:

With
Subquery_name
As
(The aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to We oversimplified example, let's replace the temporary tables with the SQL "with clause":

With
Sum_sales as
Select/*+ Materialize * *
SUM (quantity) All_sales from stores
Number_stores as
Select/*+ Materialize * *
COUNT (*) Nbr_stores from stores
Sales_by_store as
Select/*+ Materialize * *
Store_name, sum (quantity) Store_sales from
Store Natural Join Sales
SELECT
Store_name
From
Store
Sum_sales,
Number_stores,
Sales_by_store
where
Store_sales > (all_sales/nbr_stores)
;

Note the use of the Oracle undocumented ' materialize ' hint in ' with clause '. The Oracle materialize hint is used to ensure this Oracle cost-based optimizer materializes the temporary tables that Are created inside the "with" clause. This isn't necessary in oracle10g, but it helps ensure ' the tables are only created one time.

It should be noted the ' with clause ' does not yet fully-functional within Oracle SQL and it does not yet the Use the ' with clause ' replacement for ' CONNECT by ' when performing recursive queries.

To the "and clause" is used in ANSI SQL-99 syntax, this is a excerpt from Jonathan Gennick ' s great work ' Underst Anding the WITH Clause "showing", the "SQL-99" with Clause, to traverse a recursive bill-of-materials
The SQL-99 "with clause" are very confusing at the A-because SQL statement does not begin with the word SELECT. Instead, we use the ' with clause ' to start our SQL query, defining the aggregations, which can then is named in the main Q Uery as if they were "real" tables:

With
Subquery_name
As
(The aggregation SQL statement)
SELECT
(query naming subquery_name);

Retuning to We oversimplified example, let's replace the temporary tables with the SQL with "clause":

=================================================================================

Below oneself small try a hand, of course, is not complex at all, very simple very simple example, hehe.

Sql> CREATE TABLE t2 (id int); Table created. sql> CREATE TABLE t3 (id int); Table created. sql> INSERT INTO T2 values (1); 1 row created. sql> INSERT INTO T2 values (2); 1 row created. sql> INSERT into T3 values (3); 1 row created. Sql> commit; Commit complete. Sql> select * from T2; ID----------1 2 sql> select * from T3; ID----------3 sql> with 2 SQL1 as (SELECT * to T2), 3 sql2 as (SELECT * from T3) 4 select * FROM T2 5 Union 6 Selec T * from T3; SQL2 as (select * from T3) * ERROR at line 3:ora-32035:unreferenced query name defined in WITH clause-from here you can see that you have defined SQL1 And SQL2, you have to use them oh, or it will be an error. Sql> with 2 SQL1 as (SELECT * to T2), 3 sql2 as (SELECT * from T3) 4 SELECT * to SQL1 5 Union 6 select * from SQL2; ID----------1 2 3--below add a where condition try sql> with 2 sql1 as (SELECT * from T2), 3 sql2 as (SELECT * from T3) 4 Select * FR OM SQL1 5 Union 6 SELECT * FROM SQL2 7 where ID in (2,3); ID----------1 2 3--strange. Why add the Where condition or output id=1 record, continue to look down: sql> with 2 SQL1 AS (SELECT * from T2), 3 sql2 as (SELECT * from T3) 4 SELECT * to SQL1 5 where id=3 6 Union 7 SELECT * FROM SQL2 8 whe Re id=3; ID----------3--you can see that each condition is for each SELECT statement.

Just remember this first, and then you'll see a new usage.