Oracle's query for the intersection of two data tables

The ORACLE tutorial you are looking at is :Oracle's query for the intersection of two data tables.

Oracle relational database management system is the most popular relational database in the world. It is an extremely powerful, flexible and complex system. It is said that when using oracle, you should have the idea that almost any idea can be implemented in SQL.

The following is an introduction to the method of using SQL to look up the same data in two Oracle data tables. The author feels that these two methods are efficient and easy to use.

Method 1: use the operator intersect

The intersect operator is used to merge two queries and return records that exist in both queries, that is, the intersection of the results of the two queries, provided that both queries have exactly the same number of columns and data types.

Examples:

Table A data structure:


Table B data structure:



The data in table A are:

('1101 ', 'han jia'), ('1102 ', 'ding yi')

The data in table B are:

(' 1101 ', 'han jia', 99), (' 1102 ', 'ding yi', 89), (' 1103 ', 'xu jing', 94)

Run the following query in oracle, and figure 1 shows the result of this query:

[NextPage]

The second method: in clause

The in clause can create a list of values calculated by the where clause in a subquery. The difference between this approach and the previous one is that the former one USES only one intersect for multiple columns, while the former one USES an in clause to compare the first column of two subqueries, and the comparison of several columns requires the use of several in clauses. The following example shows how to obtain the intersection of two queries.

Again, using tables A and B as examples, run the following query in oracle. Figure 2 shows the results of this query:


The query results are shown in figure 2.


The above is the author's experience of using oracle 7.3, if there is any improper place, please feel free to comment.

On 1 page