Thus far, our queries have only accessed one table at a time. Queries can access multiple tables at once, or access the same table in such a way that multiple rows of the table are being processed at the same time. A query that accesses multiple rows of the same or different tables at one time is called a join query. For example, say you wish to list all the employee records together with the name and location of the associated department. To do that, we need to compare the deptno column of each row of the emp table with the deptno column of all rows in the dept table, and select the pairs of rows where these values match. This would be accomplished by the following query:
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc -------+--------+-----------+------+------------+---------+---------+--------+--------+------------+---------- 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | 10 | ACCOUNTING | NEW YORK 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | 20 | RESEARCH | DALLAS 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 | 20 | RESEARCH | DALLAS 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | 20 | RESEARCH | DALLAS 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | 20 | RESEARCH | DALLAS 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 | 30 | SALES | CHICAGO 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | 30 | SALES | CHICAGO (14 rows)
Observe two things about the result set:
There is no result row for department 40. This is
because there is no matching entry in the
emp table for
department 40, so the join ignores the unmatched
rows in the dept table. Shortly we will see how
this can be fixed.
It is more desirable to list the output columns explicitly rather than using *:
SELECT empno, ename, sal, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno;
Since all the columns had different names, the parser automatically found out which table they belong to, but it is a good style to fully qualify column names in join queries:
SELECT emp.empno, emp.ename, emp.sal, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno;
Join queries of the kind seen thus far can also be written in this alternative form:
SELECT * FROM dept INNER JOIN emp ON (emp.deptno = dept.deptno);
This syntax is not as commonly used as the one above, but we show it here to help you understand the following topics.
You will notice that in all the above results for joins no
employees were returned that belonged to department # 40.
Now we will figure out how we can get the department #40
record in. What we want the query to do is to scan the
emp table and for each row to find the
matching deptno row.
If no matching row is found we want some "empty values"
to be substituted for the emp table's columns.
This kind of query is called an outer join.
(The joins we have seen so far are inner joins.) The command looks
like this:
SELECT dept.deptno, emp.ename
FROM dept LEFT OUTER JOIN emp
ON dept.deptno = emp.deptno
ORDER BY dept.deptno;
deptno | ename | sal
--------+--------+---------
10 | CLARK | 2450.00
10 | KING | 5000.00
10 | MILLER | 1300.00
20 | SMITH | 800.00
20 | JONES | 2975.00
20 | SCOTT | 3000.00
20 | ADAMS | 1100.00
20 | FORD | 3000.00
30 | ALLEN | 1600.00
30 | WARD | 1250.00
30 | MARTIN | 1250.00
30 | BLAKE | 2850.00
30 | TURNER | 1500.00
30 | JAMES | 950.00
40 | |
(15 rows)
This query is called a left outer join because the table mentioned on the left of the join operator will have each of its rows in the output at least once, whereas the table on the right will only have those rows output that match some row of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns.
An alternative syntax of a left outer join is using the outer join operator (+) to all the columns of the emp table in the join condition within the WHERE clause. So for all the rows in the dept table that have no matching rows in the emp table, EnterpriseDB returns null for any select list expressions containing columns of emp. Hence the above example could be rewritten as:
SELECT dept.deptno, emp.ename
FROM dept, emp
WHERE dept.deptno = emp.deptno(+)
ORDER BY dept.deptno;
We can also join a table against itself. This is called a self join. As an example, suppose we wish to find the name of each employee along with the name of that employee's manager. So we need to compare the mgr column of each emp row to the empno column of all other emp rows.
SELECT e1.ename || ' works for ' || e2.ename AS "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno Employees and their Managers ------------------------------ SCOTT works for JONES FORD works for JONES ALLEN works for BLAKE WARD works for BLAKE MARTIN works for BLAKE TURNER works for BLAKE JAMES works for BLAKE MILLER works for CLARK ADAMS works for SCOTT JONES works for KING BLAKE works for KING CLARK works for KING SMITH works for FORD (13 rows)
Here we have re-labeled the emp table as
e1 and e2 to be able
to distinguish the left and right side of the join.
You can also use these kinds of aliases in other
queries to save some typing, e.g.:
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;
You will encounter this style of abbreviating quite frequently.