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

Inquiry to multiple Tables

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.

DEPTNODNAMELOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

--Combining EMP Table and DEPT Table
SELECTEMP.EMPNO,EMP.ENAME,DEPT.DEPTNO,DEPT.DNAME
FROMEMP,DEPT
WHEREEMP.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.


2. Aliases

--Combining EMP Table and DEPT Table
SELECTE.EMPNO,E.ENAME,D.DEPTNO,D.DNAME
FROMEMP E,DEPT D
WHEREE.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.

This completes [ Inquiry to multiple tables ].