How to use the exists in database--sql 2

exists summary of database SQL statements SQL exists in learning

First to compare the following syntax:

--deals= Trading tables, areas= regional tables, such as Hong Kong; Our purpose: To view the regions with transactions

SELECT * from areas where ID in (select city_id from Deals);

SELECT * from areas where ID in (select city_id from deals where deals.city_id = areas.id);

SELECT * from areas where exists (select null from deals where deals.city_id = areas.id);

Difference:

exists syntax does not say which word paragraph in the results of the sub-search, but that exists after the execution of the statement is not a record, as long as there is a record, the main query statement is established. It represents the ' existence ' of a subquery that is used to lead a nested query, it does not return any data, only produces a logical truth ' true ' with a logical false value ' false '. A subquery drawn by exists, whose target column expression is usually used with a * (or null), because a subquery with exists returns only true or false values, giving no actual meaning to the row name.

the key to performance changes:Sequencing of #1 executionWho is the driver table, who executes the query first, and who executes the query after#2 Execution ProcessThe advantage of exists is that it returns as long as it exists, so it is likely that there is no need to scan the entire table. in needs to scan the entire table and return the result. therefore, in the case of a smaller Word table, scanning the whole table and some of the table basically no difference, but in the case of large tables, exists will have an advantage. look at these two statements:--The subquery performs a full association and returns all eligible city_id

SELECT * from areas where ID in (select city_id from deals where deals.city_id = areas.id);

--The correlation of sub-query is actually the same, but the subquery can only find a result, return, so the efficiency is still relatively high

SELECT * from areas where exists (select null from deals where deals.city_id = areas.id);

Results of #3 Word table Queryexists The result of a subquery is not present, but what results are found, what fields, do not care;in requires subquery to search for the results of the main query using

the usage difference between in and exists

1. EXISTS the execution process SELECT * from t1 where exists (select null from t2 where y = x) can be understood as: For x in (SELECT * from T1) Loop if ( exists (select null from t2 where y = x.x) Then OUTPUT the RECORD End If End Loop Performance differences for in and exists : if the subquery results in fewer result set records, the table in the main query should be in if it is large and indexed, whereas if the outer main query records are small, the tables in the subquery are large and the indexes are indexed with exists. In fact, we distinguish in and exists mainly caused by the change of the driving sequence (which is the key to the performance change ), if it is exists, then the other layer table for the driver table, first accessed, if it is In, the subquery is executed first, so we will target the fast return of the driver table, then we will consider the relationship between the index and the result set .                         in addition, NULL is not processed in such as: Select 1 from dual where null in (0,1,2,null) 2.NOT in vs. not EXISTS: execution flow of not EXISTS Select ... .. From rollup R where not exists (select ' Found ' from title T where r.source_id = t.title_id); can be understood as: For x in (SELECT * from Rollup) Loop if (not exists ( so)) then OUTPUT End If; end; Note: The Not EXISTS and not are not completely interchangeable with each other, see the specific requirements. If the selected column can be empty, it cannot be replaced. For example, the following statements look at their differences: Select X, y from T; x y ------          ------ 1 3 3 1 1 2 1 1 3 1 5 select * from t where x isn't in (select y from T T2) No rows          select * from T where not exists (select null from T T2 where T2.y=t.x) x y ------   ------ 5 NULL so we need specific needs to decide Performance Differences for not in and not exists : not in a subquery only if the field after the SELECT keyword has a NOT null constraint or if there is such a hint with not, and if the table in the main query is large, the table in the subquery is small but has more records, you should use not in, and use the anti hash join. If there are fewer records in the main query table, there are many records in the subquery table, and there is an index, you can use not exists, and not in the best can also use/*+ Hash_aj */or outer joins +is NULL not -in is better in cost-based applications For example: Select ... .. From rollup R where not exists (select ' Found ' from title T where r.source_id = t.title_id); change to (good) Select ... From title T, Rollup R where r.source_id = t.title_id (+) and t.title_id is null;                                    or (good) sql> Select/*+ Hash_aj */... From rollup R where ource_id not in (select ource_id From title T where ource_id is not NULL)

Problems and Solutions

Question 1:

--users table has 1000 records, ID increment, ID is greater than 0

SELECT * from the users where exists (select * from users limit 0); --How many records are output?

SELECT * from the users where exists (select * from users where ID < 0); --How many records are output?

Answer (check view):

10,000 article

0 article

Reason:

The nature of the EXISTS query returns true as long as it encounters a record, so limit simply does not take care of it, or it does not.

Question 2:

can exists completely replace in?

No.

For example:

--Absence of associated fields: enumeration constants

SELECT * from areas where ID in (4, 5, 6);

--There is no associated field: This exists the subquery, either full true, or all false

SELECT * from areas where ID in (select city_id from deals where deals.name = ' xxx ');

An example of SQL optimization for a related exists:

9, replace in with exists (found many programmers do not know how to use this):
In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition.
in this case, using exists (or not exists) will usually improve the efficiency of the query.
Example:
(Low efficiency)
Select ... from table1 t1 where t1.id > Pno in (select No from table2 where name like ' www% ');
(Efficient)
Select ... from table1 t1 where t1.id > exists (select 1 from table2 t2 where t1.pno = t2.no and name like ' www% ');
10. substitute not exists instead of in:
in a subquery, the NOT IN clause performs an internal sort and merge.
in either case, not in is the least effective (because it performs a full table traversal of the table in the subquery).
to avoid using not, we can change it to an outer join (Outer Joins) or not exists.
11. replace distinct with exists:
When submitting a query that contains one-to-many table information, avoid using DISTINCT in the SELECT clause. You can generally consider replacing with exists
Example:
(Low efficiency)
SELECT DISTINCT D.dept_no, d.dept_name from T_dept D, t_emp e where d.dept_no = E.dept_no;
(Efficient)
Select D.dept_no, d.dept_name from T_dept D where exists (select 1 from t_emp where d.dept_no = e.dept_no);
exists makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery are met.
12. Replace the exists with the table connection:
in general, table joins are more efficient than exists.
Example:
(Low efficiency)
Select ename from the EMP e where exists (select 1 from dept where dept_no = e.dept_no and Dept_cat = ' W ');
SELECT ename
(Efficient)
Select ename from Dept D, emp e where e.dept_no = d.dept_no and Dept_cat = ' W ';

R

R

R

+

R

R

R

Database--sql in exists how to use 2 (RPM)