You can concurrently process multiple rows from one or more tables in a query. Such a query is called a join query.
For example, assume that you want to query the information about all employees and the names and locations of relevant departments based on the sample database that is created in A sample database. You must compare the deptno column of each row of the emp table with the deptno column of all rows in the dept table, and query the pairs of rows whose values match. You can execute the following statement to achieve this purpose:
SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY deptno;Sample result:
ename | sal | deptno | dname | loc
--------+---------+--------+------------+----------
MILLER | 1300.00 | 10 | ACCOUNTING | NEW YORK
CLARK | 2450.00 | 10 | ACCOUNTING | NEW YORK
KING | 5000.00 | 10 | ACCOUNTING | NEW YORK
SCOTT | 3000.00 | 20 | RESEARCH | DALLAS
JONES | 2975.00 | 20 | RESEARCH | DALLAS
SMITH | 800.00 | 20 | RESEARCH | DALLAS
ADAMS | 1100.00 | 20 | RESEARCH | DALLAS
FORD | 3000.00 | 20 | RESEARCH | DALLAS
WARD | 1250.00 | 30 | SALES | CHICAGO
TURNER | 1500.00 | 30 | SALES | CHICAGO
ALLEN | 1600.00 | 30 | SALES | CHICAGO
BLAKE | 2850.00 | 30 | SALES | CHICAGO
MARTIN | 1250.00 | 30 | SALES | CHICAGO
JAMES | 950.00 | 30 | SALES | CHICAGO
(14 rows)Take note of the following items:
We recommend that you execute the following statement to specify the output columns qualified by table name instead of using asterisks (*) or leaving out the qualification:
SELECT ename, sal, dept.deptno, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno;The deptno column must be qualified. All other columns have unique names. The parser automatically locates the table to which these columns belong. We recommend that you fully qualify column names in join queries.
The following statement is equivalent to the preceding one:
SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp INNER JOIN dept ON emp.deptno = dept.deptno ORDER BY deptno;This syntax helps you understand the following scenario.
No result row corresponds to Department 40. The join ignores unmatched rows in the dept table because no entry in the emp table matches Department 40. The following sections describe how to fix this issue.
You can execute a statement to scan each row in the dept table to find matched rows in the emp table. If no matched row is found, you can use NULL values to replace the column values in the emp table that have no matches. This type of query is called an outer join. The preceding joins are inner joins. The following statement shows an outer join:
SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept LEFT OUTER JOIN emp ON emp.deptno = dept.deptno ORDER BY deptno;Sample result:
ename | sal | deptno | dname | loc --------+---------+--------+------------+---------- MILLER | 1300.00 | 10 | ACCOUNTING | NEW YORK CLARK | 2450.00 | 10 | ACCOUNTING | NEW YORK KING | 5000.00 | 10 | ACCOUNTING | NEW YORK SCOTT | 3000.00 | 20 | RESEARCH | DALLAS JONES | 2975.00 | 20 | RESEARCH | DALLAS SMITH | 800.00 | 20 | RESEARCH | DALLAS ADAMS | 1100.00 | 20 | RESEARCH | DALLAS FORD | 3000.00 | 20 | RESEARCH | DALLAS WARD | 1250.00 | 30 | SALES | CHICAGO TURNER | 1500.00 | 30 | SALES | CHICAGO ALLEN | 1600.00 | 30 | SALES | CHICAGO BLAKE | 2850.00 | 30 | SALES | CHICAGO MARTIN | 1250.00 | 30 | SALES | CHICAGO JAMES | 950.00 | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON (15 rows)This type of query is called a left outer join. The table to the left of the join operator has each row from the table appear in the output at least once. The table to the right only has the rows that match specific rows of the left table displayed in the output. If a row of the left table does not match any rows of the right table, NULL values are used to replace the column values in the right table that have no matches.
As an alternative syntax for an outer join, you can use the outer join operator (+) in the join condition within the WHERE clause. The outer join operator is placed after the column name of the table where NULL values are used to replace unmatched rows. For all rows in the dept table that do not have matched rows in the emp table, PolarDB for PostgreSQL (Compatible with Oracle) returns NULL for any expressions in the SELECT list that include columns of the emp table. Therefore, you can rewrite the preceding statement in the following way:
SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE emp.deptno(+) = dept.deptno ORDER BY deptno;Sample result:
ename | sal | deptno | dname | loc --------+---------+--------+------------+---------- MILLER | 1300.00 | 10 | ACCOUNTING | NEW YORK CLARK | 2450.00 | 10 | ACCOUNTING | NEW YORK KING | 5000.00 | 10 | ACCOUNTING | NEW YORK SCOTT | 3000.00 | 20 | RESEARCH | DALLAS JONES | 2975.00 | 20 | RESEARCH | DALLAS SMITH | 800.00 | 20 | RESEARCH | DALLAS ADAMS | 1100.00 | 20 | RESEARCH | DALLAS FORD | 3000.00 | 20 | RESEARCH | DALLAS WARD | 1250.00 | 30 | SALES | CHICAGO TURNER | 1500.00 | 30 | SALES | CHICAGO ALLEN | 1600.00 | 30 | SALES | CHICAGO BLAKE | 2850.00 | 30 | SALES | CHICAGO MARTIN | 1250.00 | 30 | SALES | CHICAGO JAMES | 950.00 | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON (15 rows)You can also join a table with itself. This type of join is a self join. For example, if you want to query the names of employees along with the names of their managers, you can compare the mgr column of each row in the emp table to the empno column of all other rows in the emp table.
SELECT e1.ename || ' works for ' || e2.ename AS "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;Sample result:
Employees and their Managers ------------------------------ FORD works for JONES SCOTT works for JONES WARD works for BLAKE TURNER works for BLAKE MARTIN works for BLAKE JAMES works for BLAKE ALLEN works for BLAKE MILLER works for CLARK ADAMS works for SCOTT CLARK works for KING BLAKE works for KING JONES works for KING SMITH works for FORD (13 rows)In the preceding statement, the emp table is aliased as e1 to represent the employee rows in the SELECT list and in the join condition, and as e2 to represent the matched manager rows in the SELECT list and in the join condition. These types of aliases can be used to simplify statements. Example:
SELECT e.ename, e.mgr, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno ORDER BY deptno;Sample result:
ename | mgr | deptno | dname | loc --------+------+--------+------------+---------- MILLER | 7782 | 10 | ACCOUNTING | NEW YORK CLARK | 7839 | 10 | ACCOUNTING | NEW YORK KING | | 10 | ACCOUNTING | NEW YORK SCOTT | 7566 | 20 | RESEARCH | DALLAS JONES | 7839 | 20 | RESEARCH | DALLAS SMITH | 7902 | 20 | RESEARCH | DALLAS ADAMS | 7788 | 20 | RESEARCH | DALLAS FORD | 7566 | 20 | RESEARCH | DALLAS WARD | 7698 | 30 | SALES | CHICAGO TURNER | 7698 | 30 | SALES | CHICAGO ALLEN | 7698 | 30 | SALES | CHICAGO BLAKE | 7839 | 30 | SALES | CHICAGO MARTIN | 7698 | 30 | SALES | CHICAGO JAMES | 7698 | 30 | SALES | CHICAGO (14 rows)