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 such thoughts should be used when using Oracle, that is, almost any idea in SQL. . Here's how to use SQL to check the same data of two Oracle data sheet queries, the author feels that these two methods have high efficiency, easy to use. The first method: Using the operator INTERSECT INTERSECT operator to merge two queries, return the records existing in the two queries, that is, the intersection of two query results, the premise is the number and data type of the columns of the two queries. It must be exactly the same. For example: the data structure of Table A:
Chinese name field name type length student number CODEC 4 name NAMEC 8
Table B data structure:
Chinese name field name type length student number student_code c 4 name student_namec 8 score SCOREN 3
The data of Table A is: ('1101', 'Han A'), ('1102', 'Dishi "Table B data is: (' 1101 ',' Harn ', 99), (' 1102 ' , 'Ding B', 89), ('1103', 'Xu Jing', 94) run the following query in Oracle, Figure 1 shows the results of this query:
SQL> Select Code, Name from aintersectSelect Student_code, Student_name from B;
Figure 1 Result using the INTERSECT operator query
The second method: IN clause IN clause can create a list for the value to calculate the WHERE clause in the subquery. This approach is different from the previous method. The former method is more than a multi-column but only one intertent is used, and a in clause is used to compare a column of two subquers, and a few columns should be used IN clause. The following example shows how to get the intersection of two queries. Still use A and B two data sheets as an example, run the following query in Oracle, Figure 2 shows the results of this query:
SQL> Select Code, Name from Awhere A.code in (Select Student_code from b) and a.name in (select student_name from b) Order by a.code;
The results of the query are shown in Figure 2.
Figure 2 Results using IN clause query
The above uses Oracle7.3 experience, if there is something wrong, please let me know.
