Multi-Table query using SQL Author: unknow Source: reposted online; Updated:-11-15 views:
Brief description: Multi-Table query using SQL
Multi-Table query is relative to a single table. It refers to querying data from multiple data tables. Here we mainly learn how to query data from two data tables.
4.3.1 unconditional multi-Table query
Unconditional multi-Table query combines the records of each table in the form of a Cartesian product.
For example, there are 4 records in the Scott. Dept table and 14 records in the Scott. EMP table. The Cartesian product contains 4*14 = 56 Records.
In the command editing area, run the following statements.
――――――――――――――――――――――――――――――――――――――
Select EMP. empno, EMP. ename, EMP. deptno, Dept. dname, Dept. LOC from Scott. EMP, Scott. dept ;――――――――――――――――――――――――――――――――――――――
Click execute. The result shown in 4.16 is displayed.
4.3.2 equivalent multi-Table query
in the Command editing area, run the following statements.
―――――――――――――――――――――――――――――――――――――
select EMP. empno, EMP. ename, EMP. deptno, Dept. dname, Dept. LOC from Scott. EMP, Scott. dept where Scott. EMP. deptno = Scott. dept. deptno;
―――――――――――――――――――――――――――――――――――――
click execute, the result shown in 4.17 is displayed.
an equivalent multi-Table query queries the data associated with multiple data tables based on the equivalent conditions. Certain fields of multiple associated data tables must have the same attributes, that is, they must have the same data type, width, and value range.
4.3.3 Non-equivalent multi-Table query
in the Command editing area, run the following statements.
―――――――――――――――――――――――――――――――――――――
select EMP. empno, EMP. ename, EMP. deptno, Dept. dname, Dept. LOC from Scott. EMP, Scott. dept where Scott. EMP. deptno! = Scott. dept. deptno and Scott. EMP. deptno = 10; --
click execute, the result shown in 4.18 is displayed.
in non-equivalent multi-table queries, you can use the comparison operator shown in table 4.1 to combine query conditions.