Oracle in and EXISTS statements

Official documents:
home/database/oracle Database Online Documentation 11g Release 2 (11.2)/Database administration

EXISTS Condition

An EXISTS condition tests for existence of rows in a subquery.

The EXISTS condition tests whether rows exist in the subquery.

EXISTS (SQL returns the result set as true)
Not EXISTS (SQL does not return result set is true)

Experiment:

CREATE TABLE SCOTT.A (ID number (), name varchar (32));
INSERT INTO SCOTT.A
Select 1, ' A1 ' from dual
UNION ALL
Select 2, ' A2 ' from dual
UNION ALL
Select 3, ' A3 ' from dual;
Commit

CREATE TABLE scott.b (ID number), aid number (Ten), name VARCHAR2 (32));

INSERT INTO scott.b
Select, ' B1 ' from dual
UNION ALL
Select 2,2, ' B2 ' from dual
UNION ALL
Select 3,2, ' B3 ' from dual;
Commit

[Email Protected]>select * from SCOTT.A;

ID NAME
---------- --------------------------------
1 A1
2 A2
3 A3

[Email Protected]>select * from scott.b;

ID AID NAME
---------- ---------- --------------------------------
1 1 B1
2 2 B2
3 2 B3

Table A and Table B is a 1:b relationship, how to look at it?

You only need to summarize statistics on the two Table association columns to know what the two tables are: summarize the associated columns and order by

[Email protected]>select id,count (*) from the SCOTT.A Group by ID ORDER by COUNT (*) desc;

        id   COUNT (*)
--------------------
          1          1
          3          1
          2          1

[email  protected]>select Aid,count (*) from SCOTT.B Group by aid ORDER by COUNT (*) desc;

       aid   COUNT (*)
--------------------
          2          2
          1          1

The value of the count (*) of the B table is greater than 1, which is the n:1 relationship, the value of count (*), all 1 is the 1:1 relationship, and COUNT (*) has more than 1 is the 1:n relationship

Emp:dept N:1 Returns a relationship of N and does not return a 1 relationship

View equivalent overrides:

[Email protected]>select id,name from scott.a where ID in (select aid from scott.b);

ID NAME
---------- --------------------------------
1 A1
2 A2

The association column is a.id = B.aid

The above query uses the in statement, in () executes only once, it detects all the ID fields in table B and caches them. After that, check that the ID of table A is equal to the ID in table B, and if it is equal, add the records of table A to the result set until all records of table A are traversed.

As you can see, it is not appropriate to use in () when the table B data is large, because it iterates through the B-table data all at once.

Equivalent rewrite:

Select Id,name from SCOTT.A exists (select 1 from scott.b where a.id=b.aid);

The above query uses the EXISTS statement, exists () executes a.length times, and it does not cache the exists () result set because the contents of the exists () result set are not important, it is important whether there is a record in the result set, and if so, returns True if none returns False .

Conclusion: Exists () is suitable for the case of B-table larger than a-table data

When the A-table data is as large as the B-table data, in and exists efficiency is similar, can choose one to use.

Oracle in and EXISTS statements