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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.