9.3.1 Outer Join Operator (+)

In the WHERE clause conditional expression, by adding the plus sign (+), which is the outer join operator, to the column of the table you want to add as a table join, it is possible to achieve an outer join that is the same as a joined table (OUTER JOIN).

Specification format

SELECT statement

Join condition

Note

Here we are dealing only with the WHERE clause of the SELECT statement. Refer to "SQL Commands" in "Reference" in the PostgreSQL Documentation for information on the overall specification format of the SELECT statement.

General rules

WHERE clause

The WHERE clause specifies search condition or join conditions for the tables that are derived.
Search conditions are any expressions that return BOOLEAN types as the results of evaluation. Any lines that do not meet these conditions are excluded form the output. When the values of the actual lines are assigned to variables and if the expression returns "true", those lines are considered to have met the conditions.
Join conditions are comparison conditions that specify outer join operators. Join conditions in a WHERE clause return a table that includes all the lines that meet the join conditions, including lines that do not meet all the join conditions.
Join conditions are prioritized over search conditions. For this reason, all lines returned by the join conditions are subject to the search conditions.

The following rules and restrictions apply to queries that use outer join operators. We therefore recommend the use of FROM clause joined tables (OUTER JOIN) rather than outer join operators:

Outer join operators can only be specified in the WHERE clause.
Outer join operators can only be specified for base tables or views.
To perform outer joins using multiple join conditions, it is necessary to specify outer join operators for all join conditions.
When combining join conditions with constants, specify outer join operators in the corresponding column specification. When not specified, they will be treated as search conditions.
The results column of the outer join of table t1 is not returned if table t1 is joined with table t2 by specifying an outer join operator in the column of t1, then table t1 is joined with table t3 by using search conditions.
It is not possible to specify columns in the same table as the left/right column specification of a join condition.

It is not possible to specify an expression other than a column specification for outer join operators, but they may be specified for the columns that compose the expression.

There are the following limitations on the functionality of outer join operators when compared with joined tables (OUTER JOIN). To use functionality that is not available with outer join operators, use joined tables (OUTER JOIN).

Table 9.2 Range of functionality with outer join operators
Functionality available with joined tables (OUTER JOIN)	Outer join operator
Outer joins of two tables	Y
Outer joins of three or more tables	Y (*1)
Used together with joined tables within the same query	N
Use of the OR logical operator to a join condition	N
Use of an IN predicate to a join condition	N
Use of subqueries to a join condition	N

Y: Available
N: Not available

*1: The outer joins by outer join operators can return outer join results only for one other table. For this reason, to combine outer joins of table t1 and table t2 or table t2 and table t3, it is not possible to specify outer join operators simultaneously for table t2.

Example

Table configuration

col1	col2	col3
1001	AAAAA	1000
1002	BBBBB	2000
1003	CCCCC	3000

col1	col2
1001	aaaaa
1002	bbbbb
1004	ddddd

Example 1:

In the following example, all records in table t2 that include ones that do not exist in table t1 are returned:

SELECT * 
  FROM t1, t2
  WHERE t1.col1(+) = t2.col1;
 col1 |    col2    | col3 | col1 |    col2
------+------------+------+------+------------
 1001 | AAAAA      | 1000 | 1001 | aaaaa
 1002 | BBBBB      | 2000 | 1002 | bbbbb
      |            |      | 1004 | ddddd
(3 rows)

This is the same syntax as the joined table (OUTER JOIN) of the FROM clause shown next.

SELECT * 
  FROM t1 RIGHT OUTER JOIN t2
       ON t1.col1 = t2.col1;

Example 2:

In the following example, the results are filtered to records above 2000 in t1.col3 by search conditions, and the records are those in table t2 that include ones that do not exist in table t1. After filtering with the join conditions, there is further filtering with the search conditions, so there will only be one record returned.

SELECT * 
  FROM t1, t2
  WHERE t1.col1(+) = t2.col1
    AND t1.col3 >= 2000;
 col1 |    col2    | col3 | col1 |    col2
------+------------+------+------+------------
 1002 | BBBBB      | 2000 | 1002 | bbbbb
(1 row)

This is the same syntax as the joined table (OUTER JOIN) of the FROM clause shown next.

SELECT * 
  FROM t1 RIGHT OUTER JOIN t2
       ON t1.col1 = t2.col1
  WHERE t1.col3 >= 2000;