Oracle query for two data table intersections _oracle

The Oracle tutorial being looked at is Oracle's query that intersects two data tables.

Oracle relational database management system is a popular relational database in the world, it is an extremely powerful, flexible and complex system, it is said that in the use of Oracle should be the idea, that is in SQL can almost achieve any idea.

The following is a method of using SQL to search for the same data in two Oracle datasheet queries, and the author feels that the two methods are efficient and easy to use.

The first method: the use of 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.

An example is provided:

Data structure of Table A:


Data structure of Table B:



The data for table A is:

(' 1101 ', ' Han Jia '), (' 1102 ', ' butylacetate ')

The data for Table B is:

(' 1101 ', ' Han Jia ', 99), (' 1102 ', ' Butylacetate ', 89), (' 1103 ', ' xujing ', 94)

Run the following query in Oracle, Figure 1 shows the results of this query:

[NextPage]

The second method: in clause

In clause you can create a list in a subquery for values computed by the WHERE clause. This method differs from the previous method by comparing multiple columns with only one intersect, while an in clause is used to compare a column of two subqueries, and several columns are used in a few in clauses. The following example shows how to get the intersection of two queries.

Still, take A and B tables as an example, run the following query in Oracle, Figure 2 shows the results of this query:


The query results are shown in Figure 2.


The above for the author uses oracle7.3 experience, if has the improper place, please not hesitate to enlighten.

Previous page