Oracle takes the intersection of data from two tables and sets the difference collection

Oracle takes the intersection of data from two tables

Keyword: Oracle takes the intersection of data from two tables INTERSECT

Oracle, as a large relational database, often needs to extract the intersection data of two tables in daily applications.

For example, the following table is required to find the salary 2500 (excluding 2500) and the male (M) employee number, 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: Use the operator intersect

The INTERSECT operator is used to merge two queries, returning records that exist in two queries, that is, the intersection of two query results, provided that the number of columns for 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

Results 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 commonly used methods, especially the second one, fully reflect the essence of the relationship data. But if the structure of the two tables is more complex and the relational conditions are difficult to implement, the first is clearly a good way to get the results you need easily.

Difference collection: minus

Union, and exclude duplicate records: unions

Union, and contains duplicate records: Unions all

Transferred from: http://blog.sina.com.cn/s/blog_5656bf3e010007cz.html

Oracle takes the intersection of data from two tables and sets the difference collection