Inquiry to multiple Tables - SQL Strategy - A site to master SQL while executing it on the Web

In this section, you will learn how to retrieve data from multiple Tables using SELECT statements.

1.Conditional coupling

From now on, the DEPT (department) Table will be used in addition to the existing EMP Table.

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

--Combining EMP Table and DEPT Table

SELECT	EMP.EMPNO,EMP.ENAME,DEPT.DEPTNO,DEPT.DNAME
FROM	EMP,DEPT
WHERE	EMP.DEPTNO = DEPT.DEPTNO

When querying from multiple Tables, use a FROM followed by the Table Name separated by "," (comma).

DEPTNO = DEPT.DEPTNO in the WHERE condition, meaning that the columns where DEPTNO in the EMP Table is equal to DEPTNO in the DEPT Table are concatenated.

The reason why the item name immediately following the SELECT statement contains the statement "Table明. The reason for the item name "TableMing. itemName" is to specify which item should be displayed if the Table is concatenated and both Tables have the same item name. (If only "DEPTNO" is specified, the DBMS will not know which item to display and an error will result.)

To further narrow down the criteria from the concatenated Table, write AND EMP.SAL > 1500 in the WHERE condition.

Now for the Practice, I will give you two questions.

Practice (1/2) Questions

Join the EMP Table and DEPT Table with DEPTNO and display ENAME from the EMP Table and DNAME from the DEPT Table.

Practice (1/2) Answers

SELECT	EMP.ENAME,DEPT.DNAME
FROM	EMP,DEPT
WHERE	EMP.DEPTNO = DEPT.DEPTNO0

There is no problem as SELECT ENAME,DNAME.

Practice (2/2) Questions

Combine the EMP Table and DEPT Table with DEPTNO and display ENAME and SAL from the EMP Table and DNAE from the DEPT Table for employees with a SAL of 1500 or more.

Practice (2/2) Answers

SELECT	EMP.ENAME,EMP.SAL,DEPT.DNAME
FROM	EMP,DEPT
WHERE	EMP.DEPTNO = DEPT.DEPTNO AND EMP.SAL >= 1500

The set function, given a WHERE condition, finds values only from records that meet that search condition.

2. Aliases

--Combining EMP Table and DEPT Table

SELECT	E.EMPNO,E.ENAME,D.DEPTNO,D.DNAME
FROM	EMP E,DEPT D
WHERE	E.DEPTNO = D.DEPTNO

The above SQL is exactly the same as "conditional join". The difference is that the DB reassignment (alias) is added to the Table to simplify the description.

In practice, however, there are very long table names such as "Total Sales of All Stores at the End of the Previous Month". In such cases, it is very useful.

Now for the Practice, I will give you one Question.

Practice (1/1) Question

Combine the EMP Table and DEPT Table with the alias DEPTNO and display ENAME and DEPTNO from the EMP Table and DNAME and DEPTNO from the DEPT Table.

Practice (1/1) Answer

SELECT	E.ENAME,E.DEPTNO,D.DEPTNO,D.DNAME
FROM	EMP E,DEPT D
WHERE	E.DEPTNO = D.DEPTNO

The alias can be any Name.

This completes [ Inquiry to multiple tables ].