Selecting data from multiple tables

Equi join

We want to obtain the following: for each employee to find his name, occupation (Table EMP) and the name of the department where you work (Table DEPT).

In both of these tables is DEPTNO, stating the number of the department and containing the same values, talking about the relationship between these tables.

Relation based on a relation of equality is called an equivalence relationship (equi-join).

The condition of equality is recorded as a normal condition, except that the values ​​for comparison are taken from different tables.

To join together logically EMP and DEPT tables, write:

SELECT ENAME, JOB, DNAM
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

Together with the information taken from the table EMP departments get the names taken from the table DEPT. Rows of the table EMP is combined with such lines of the table DEPT, which EMP.DEPTNO value coincides with the DEPT.DEPTNO.

Preceding the column name, table name is required only if listed in the tables in the FROM clause is a column with the same name (DEPTNO). Specifying the column name would be ambiguous. Although this value for both the table are the same reasons, the syntax is required to display a table from which the value will be charged.

In the example below in the SELECT clause is required to appeal to the DEPT table:

SELECT DEPT.DEPTNO, ENAME, JOB, DNAM
FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY DEPT.DEPTNO;

Table Aliases

Aliases, or name a replacement, facilitate the writing of queries. Aliases are defined in the FROM clause. They apply only in the query, in which they are defined. You should use the alias in the SELECT clause, although lyrically it occurs earlier than the FROM clause.

Example of use of aliases:

SELECT D.DEPTNO, ENAME, DNAM
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO;

If an alias is defined for a table, then the original table name may no longer be used in this query.

Non equi join

Joins inequality (non-equi-join) are not based on a relation of equality. The relation between the rows of two tables is determined by means other than the equality operator.

For example, the relation between tables EMP and SALGRADE is based on the following principles: determination of the rate of worker classification is based on the indication for which the interval (LOSAL, HISAL) are his wages.

To create this condition we use BETWEEN ... AND operator.

SELECT ENAME, SAL
FROM EMP E S.GRADE, SALGRADE S
WHERE SAL AND BETWEEN LOSAL HISAL;

The rules for combining tables

The general principle of joining tables:

The minimum number of connecting conditions = number of tables minus 1