Exists and in efficiency and usage in oracle

Exists and in efficiency and usage in oracle

Replace not in with not exists

Select * from tsp_product p where not exists (select ''from tsp_orderitem I where p. id = I. product_id)
Select * from tsp_product p where id not in (select product_id from tsp_orderitem I where p. id = I. product_id)
Replace in with exists
Select * from tsp_product p where p. id in (select product_id from tsp_orderitem)
Select * from tsp_product p where exists (select 'x' from tsp_orderitem I where p. id = I. product_id)

Next, we will analyze why not exists is used to replace not in.

There are two simple examples to illustrate the efficiency of "exists" and "in ".

1) select * from t1 where exists (select 1 from t2 where t1.a = t2.a );

Small Data size of t1 and large data size of t2, t1 <t2, 1) high query efficiency.

2) select * from t1 where t1.a in (select t2.a from t2 );

T1 has a large data volume and t2 has an hour, t1> t2, 2) High query efficiency.

 

Union
Combine the data of the two tables. If there are duplicate rows, only one row is taken.
Union all
Combine the data of the two tables without filtering duplicate rows.
Minus
Returns the row records that are different from the results of the second query.

Oracle has such conventions
1. The select statement must return the same number of columns. If the number of columns is different, you can select a string instead of a column.
2. The corresponding columns in the select statement must have the same data type and the length can be different.

Rollup

Statistics: select sum (s. totalamount), to_char (createdate, 'yyyy-mm') from tsp_orders s group by rollup (to_char (createdate, 'yyyy-mm ')

Connect
Select level, a. * from tsp_area a start with parent_id is null connect by prior id = parent_id

Creates a similar tree report.
Prior forces the order of reports to change from root to leaf (if prior is parent) or from leaf to root (if prior is a descendant)
Although the where clause can exclude the upper part of the human tree, it cannot exclude their children and grandchildren (or the ancestor, if prior is on the right of the equal sign)

Let's take a look at its differences.

Exists usage:

Note: 1) the section in the sentence contains a color font to understand its meaning;

"Select 1 from t2 where t1.a = t2.a" is equivalent to a join Table query, which is equivalent

"Select 1 from t1, t2 where t1.a = t2.a"

However, if you execute the statement in parentheses (1), a syntax error will be reported, which is also worth attention when using exists.

"Exists (xxx)" indicates whether the statement in parentheses can identify the record and whether the record to be queried exists.

Therefore, the "1" in "select 1" is irrelevant. It is okay to replace it with "*". It only cares whether the data in the brackets can be searched out, whether such a record exists. If so, the where condition of the sentence is true.

Usage of in:

Continue to reference the above example

"2) select * from t1 where t1.a in (select t2.a from t2 )"

The content of the field searched by the statement following the "in" must correspond to each other. Generally, the expression of field a in Table t1 and table t2 must be the same, otherwise, this query is meaningless.

For example, table t1 and table t2 have a field indicating the ticket number. However, table t1 indicates that the ticket number field is named "ticketid" and table t2 indicates "id ", however, the expression is the same, and the data format is the same. In this case, use the 2) method as follows:

"Select * from t1 where t1.ticketid in (select t2.id from t2 )"

Select name from employee where name not in (select name from student );

Select name from employee where not exists (select name from student );