Other methods of joining tables

Outer join

When connecting a row of tables in the table with his counterpart in the second table is selected. For example, in the example of combining EMP and DEPT table by column DEPNO not been selected row of the table DEPT where DEPTNO = 40 (Department Operations), because no one works in that department.

Let's try to improve this connector so that the department was also chosen 40th We will do this by joining the external (outer-join), where a row in the table DEPT, which has no counterpart in the table EMP is combined with the EMP table row containing NULL values ​​the same (although the poem does not actually exist).

Marked with an external connector () on the side of equality, which refers to a table with incomplete information.

SELECT ENAME, D.DEPTNO, DNAM
FROM EMP E, DEPT D
WHERE E.DEPTNO () = D.DEPTNO;

Outer join operator can appear only on one side of equality.

Table connection with other

Thanks aliases same table, we can connect with each other. Consider the following example - we want to select employees who earn less than their managers:

SELECT E.ENAME EMP_NAM, E.SAL EMP_SAL, M.ENAME NAM_LOO, M.SAL SAL_LOO
FROM EMP E, EMP M
WHERE AND E.MGR = M.EMPNO E.SAL;

As seen in the FROM clause, canceled a twice into the same table with two aliases E and M.