Shulou(Shulou.com)06/01 Report--

Exits function of oracle

Select t.fene

From fenebiao t

Where t.code = '001' and date = to_date (' 2017-07-17, 'yyyy-mm-dd')

And exists (select 1 from cszqxx b

Where b.fzqdm = substr (t.fkmbm, 9,6)

And b.fscdm = '100253')

Analysis: you can find the fene (share) information of all bonds of the 001 fund from fenebiao, and now you need to query the share information of '100253' bonds; but there is no fscdm field in fenebiao, which exists in the cszqxx table, and the corresponding relationship between cszqxx and fenebiao is b.fzqdm = substr (t.fkmbm, 9,6); previously, you used left join, and there are two pieces of information in the '100253' cszqxx table, so you will query two fene. Using exits filters the contents of the main table fenebiao based on the return value in the exits function.

In addition, the following content comes from Baidu:

Table A (small table), table B (large table)

In the statement select * from B where cc in (select cc from A), we first find the cc from table A, and then find the relevant cc in table B according to cc, because the cc of table An is much smaller than the cc of table B, so it can save time.

The phrase select * from B exists (select cc from A where cc=B.cc) is to find the cc in table B and then find the relevant cc in table A because the cc of table B is much more than the cc of table A, so it is a waste of time to do so.

Summary: when using in, the big table is in the front and the small table is in the back.

When using exists, the small table is at the front and the big table is at the back.

two。

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.