How to use with as in sql statement

WITH AS phrase, also called subquery part, can define a SQL fragment that will be used by the entire SQL statement. It can make the SQL statement more readable, or it can be used as the part that provides data in different parts of UNION ALL.

– for one alias

with tmp as (select * from tb_name)

– for multiple aliases

with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
…

– equivalent to building an e temporary table

with e as (select * from scott.emp e where e.empno=7499)
select * from e;

-It is equivalent to building e and d temporary tables

with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

In fact, it is to put a lot of repeatedly used sql statements in with as, take an alias, and use it in subsequent queries. , which plays an optimization role in large batches of SQL statements and is clear.

With as usage of inserting data into a table

insert into table2
with
s1 as (select rownum c1 from dual connect by rownum <= 10),
s2 as (select rownum c2 from dual connect by rownum <= 10)
select a.c1, b.c2 from s1 a, s2 b where…;

select s1.sid, s2.sid from s1, s2 needs to have associated conditions, otherwise the result will be a Cartesian product.

with as is equivalent to a virtual view.

With as phrase, also called subquery factoring, allows you to do a lot of things. Define a sql fragment that will be used by the entire sql statement. Sometimes, it is to make the SQL statement more readable, or it may be in different parts of union all as the part that provides data.

Especially useful for union all. Because each part of union all may be the same, but if each part is executed once, the cost is too high, so you can use the with as phrase, which only needs to be executed once. If the table name defined by the with as phrase is called more than twice, the optimizer will automatically put the data obtained by the with as phrase into a temp table. If it is only called once, it will not. The prompt materialize forces the data in the with as phrase to be put into a global temporary table. Many queries can be speeded up this way.

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 exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select ‘no records’ from dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);

Advantages of WITH statement:

(1). SQL readability is enhanced. For example, give a meaningful name to a specific with subquery, etc.

(2). The with subquery is only executed once, and the results are stored in the user temporary table space, which can be referenced multiple times to enhance performance.

Example: During the process of importing EXCEL, sometimes the data needs to be stored in a temporary table. The next time the import is performed, the data in the temporary table will be cleared. However, at this time, sometimes If there is a concurrency problem, two users may operate each other's data separately, so it may cause confusion. However, you can use the WITH function and the UNION statement to splice a SQL statement and store it in SESSION. When you need to export error information, you can use this Statements construct data.

Related recommendations: "mysql tutorial"

The above is the detailed content of How to use with as in sql statement. For more information, please follow other related articles on the PHP Chinese website!