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:
Chinese name |
Field name |
Type |
Length |
Student ID |
Code |
C |
4 |
Name |
Name |
C |
8 |
Data Structure of Table B:
Chinese name |
Field name |
Type |
Length |
Student ID |
Student_code |
C |
4 |
Name |
Student_name |
C |
8 |
Score |
Score |
N |
3 |
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. Figure 1 shows the query result:
SQL> Select Code, name from Intersect Select student_code, student_name from B; |
Figure 1 query results using the Intersect Operator |
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. Figure 2 shows the query result:
SQL> Select Code, name from Where a. Code in (Select student_code from B) And a. Name in (Select student_name from B) Order by A. Code; |
The query result 2 is shown.
Figure 2 query results using the in Clause |
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.