Oracle takes the intersection of data in two tables __oracle

keywords: Oracle takes the intersection of data in two tables INTERSECT

Oracle as a large relational database, often need to extract the intersection data of two tables in daily application

For example, if you need to find an employee number with a wage of 2500 (excluding 2500) and a male (M), then use the relationship between the two tables to make an intersection.

Employee

CODE	NAME	GENDER
001	Tom	M
002	Jerry	M
003	Ana	F

Salary

CODE	SALARY
001	2800
002	2500
003	3000

Method 1: Using the operator intersect

The INTERSECT operator merges two queries, returns the records that exist in two queries, and returns the intersection of two query results, provided that the number of columns in two queries and the data type must be identical.

SELECT CODE from EMPLOYEE WHERE GENDER = ' M '
INTERSECT
SELECT CODE from SALARY WHERE SALARY > 2500

The result of the first SQL is

CODE
----------
001
002

The result of the second SQL is

CODE
----------
001
003

The result of INTERSECT

CODE
----------
001

Method 2: Leverage relational SQL

SELECT A.code from EMPLOYEE A, SALARY B
WHERE A.code = b.code and A.gender = ' M ' and b.salary > 2500

Result is

CODE
----------
001

The above two methods are my more common methods, especially the second, fully embodies the essence of the relationship data. But if the two-table structure is more complex and the relationship conditions are difficult to implement, the first is clearly a good way to get the desired results simply.

If you use exists words or use the first kind.

Let's look at the following example: Two system tables in Oracle. Emp,dept.

Example

1: NOT EXISTS ( not in)

Not exists:

This statement returns the result set that the select * from Scott.dept D where e.deptno=d.deptno and d.deptno=10 conditions satisfy. In other words,

There is no record of the d.deptno=10 result set in the returned result sets, that is, there is no record of d.deptno=10 in the Dept table in the EMP table.

 SQL> Select Empno,ename,deptno from Scott.emp e where does exists (SELECT * from Scott.dept D where e.deptno=d.d Eptno and d.deptno=10);

EMPNO ename DEPTNO
----- ---------- ------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7788 SCOTT 20
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20

Rows selected

Not in :

The first where condition must be given a field (DEPTNO), and in the second SQL statement, it must be clear which (DEPTNO) the field to query is given.

Sql> Select Empno,ename,deptno from Scott.emp e where deptno not in (select Deptno from Scott.dept D where e.deptno=d.de Ptno and d.deptno=10);

EMPNO ename DEPTNO
----- ---------- ------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7788 SCOTT 20
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20

Rows selected

2: exists (in)

exists:

This statement returns the set of record results that are satisfied by the SELECT * from Scott.dept D where e.deptno=d.deptno and d.deptno=10 conditions.

That is, there are only d.deptno=10 records in the returned result set, that is, only d.deptno=10 records in the Dept table exist in the EMP table.

Sql> Select Empno,ename,deptno from Scott.emp e where exists (SELECT * from Scott.dept D where E.deptno=d.deptno and D. DEPTNO=10);

EMPNO ename DEPTNO
----- ---------- ------
7782 CLARK 10
7839 KING 10
7934 MILLER 10

In :

The first where condition must be given a field (DEPTNO), and in the second SQL statement, it must be clear which (DEPTNO) the field to query is given.

Sql> Select Empno,ename,deptno from Scott.emp e where deptno into (select Deptno from Scott.dept D where E.DEPTNO=D.DEPTN O and d.deptno=10);

EMPNO ename DEPTNO
----- ---------- ------
7782 CLARK 10
7839 KING 10
7934 MILLER 10

in Oracle, exists (in) and not exists are the key to determining the existence and absence of records in a table.

Note that not in logic is not exactly equivalent to not exists, and if you misuse not in, be careful that your program has a fatal bug: So try not to use not in (it will call the subquery), and try to use NOT EXISTS (it will call the associated subquery).