Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.