Introduction to the usage of with as in sql statement

This article introduces the relevant knowledge of "introduction to the use of with as in sql sentences". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The WITH AS phrase, also known as the subquery section, can define a SQL fragment that is used by the entire SQL statement. You can make the SQL statement more readable, or it can be used as a part of providing data in different parts of UNION ALL.

-for an alias

With tmp as (select * from tb_name)

-for multiple aliases

Withtmp 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

-equivalent to the establishment of e, d temporary tables

Withe 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, the following query can use it, so for a large number of sql statements to play an optimization role, and clear.

With as usage of inserting data into a table

Insert into table2withs1 as (select rownum C1 from dual connect by rownum