Queries that intersect two data tables in Oracle

oracle| Data 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 the 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:

Chinese Name field Name Type Length School number CodeC 4 name Namec 8
Data structure of Table B:

Chinese Name field Name type length number Student_code C 4 name Student_namec 8 score Scoren 3

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:

Sql>select code, name from A
Intersect
Select Student_code,student_name from B;

Figure 1 The results of the query using the INTERSECT operator
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:

Sql>select Code,name from A
where A.code in
(select Student_code from B)
and A.name in
(select Student_name from B)
Order BY A.code;
The query results are shown in Figure 2.


Figure 2 Results of query using in clause
The above for the author uses oracle7.3 experience, if has the improper place, please not hesitate to enlighten.