Oracle query of intersection of two data tables

Oracle query of intersection of two data tables

Oracle query of intersection of two data tables

The ORACLE tutorial is: query the intersection of two data tables in Oracle.

Oracle relational database management system is a popular relational database system in the world. It is an extremely powerful, flexible, and complex system. It is said that this idea should be applied to oracle, that is, almost any idea can be implemented in SQL.

The following describes how to use SQL to query the same data in two Oracle data tables. I feel that these two methods are highly efficient and easy to use.

Method 1: Use the intersect Operator

The intersect operator is used to merge two queries and return records in both queries, that is, the intersection of the two query results, provided that the number and Data Type of the two query columns must be identical.

Example:

Data Structure of Table:


Data Structure of Table B:



The data in table A is:

('123456', 'Han jia'), ('1234568', 'ding yi ')

The data in table B is:

('20140901', 'hangzhou', 99), ('20140901', 'tingyi', 89), ('20170901', 'xujing', 94)

Run the following query in oracle to display the query results:

[NextPage]

Method 2: in Clause

The in clause can create a list for the values calculated by the where clause in the subquery. This method differs from the previous method in that the previous method compares multiple columns but only uses one intersect, and an in clause is used to compare one column of two subqueries, to compare several columns, you must use several in clauses. The following example shows how to obtain the intersection of two queries.

Take Data Tables A and B as an example. Run the following query in oracle and the query result is displayed:


The query result 2 is shown.


The above is the author's experience in using oracle7.3. If you have any improper information, please do not give us any further advice.

Previous Page