Guide to the Oracle (+) operator - PolarDB - Alibaba Cloud Documentation Center

During a database migration or compatibility development, you may encounter the Oracle-specific (+) outer join syntax. This syntax indicates an outer join by adding the (+) symbol to a column name in the WHERE clause, which differs from the standard LEFT JOIN or RIGHT JOIN syntax. To help you understand and use this legacy syntax, PolarDB for PostgreSQL (Compatible with Oracle) provides compatibility support for the (+) operator, ensuring that existing code can be migrated and run smoothly.

Overview

The (+) operator is a legacy Oracle syntax for outer joins. It defines a left or right outer join by placing the (+) symbol in the join condition of the WHERE clause. The symbol is placed on the side of the table whose columns are to be filled with NULL values.

Left outer join (LEFT OUTER JOIN)
Place the (+) operator on the join column of the right table. The right table is the one that is supplemented with NULL values.
```
SELECT columns FROM table1, table2 WHERE table1.column = table2.column(+);
```
This is equivalent to the standard SQL syntax:
```
SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
```
Right outer join (RIGHT OUTER JOIN)
Place the (+) operator on the join column of the left table. The left table is the one that is supplemented with NULL values.
```
SELECT columns FROM table1, table2 WHERE table1.column(+) = table2.column;
```
This is equivalent to the standard SQL syntax:
```
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
```

Notes

The (+) operator can appear only in the WHERE clause, not in the ON clause.
In a join condition, the (+) operator can appear on only one side of the equals sign. If a query contains multiple join conditions for the same table, the (+) operator must be used consistently.
A join condition that contains the (+) operator cannot be combined with other conditions using the OR logical operator.
PolarDB for PostgreSQL (Compatible with Oracle) supports the (+) syntax. However, for better readability and cross-database compatibility, we recommend that you use the standard LEFT JOIN and RIGHT JOIN syntax in new projects.

Examples

The following examples show common uses of the (+) operator.

Prepare data

Before you run the examples, connect to your PolarDB cluster and create the following test tables.

-- Create the employee table
CREATE TABLE emp (
    empno  NUMBER(4) PRIMARY KEY,
    ename  VARCHAR2(20),
    deptno NUMBER(2),
    sal    NUMBER(7,2)
);

-- Create the department table
CREATE TABLE dept (
    deptno NUMBER(2) PRIMARY KEY,
    dname  VARCHAR2(20),
    loc    VARCHAR2(20)
);

-- Insert data into the department table
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON'); -- This is a department with no employees

-- Insert data into the employee table
INSERT INTO emp VALUES (7369, 'SMITH', 20, 800);
INSERT INTO emp VALUES (7499, 'ALLEN', 30, 1600);
INSERT INTO emp VALUES (7782, 'CLARK', 10, 2450);
INSERT INTO emp VALUES (7839, 'KING', 10, 5000);
INSERT INTO emp VALUES (8000, 'TEMP', NULL, 1000); -- This is an employee with no department

COMMIT;

Example 1: Perform a left outer join

Query for all employees and their department information. The results must include employees who are not assigned to a department, which means their deptno is NULL.

-- Use (+) to perform a left outer join. The (+) is placed on the column of the right table (dept).
SELECT e.ename, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno(+)
ORDER BY e.empno;

The query returns the following result:

 ename | sal  |   dname    |   loc    
-------+------+------------+----------
 SMITH |  800 | RESEARCH   | DALLAS
 ALLEN | 1600 | SALES      | CHICAGO
 CLARK | 2450 | ACCOUNTING | NEW YORK
 KING  | 5000 | ACCOUNTING | NEW YORK
 TEMP  | 1000 |            | 
(5 rows)

Result description:
The query results include all employees from the emp table. The employee named TEMP has a deptno of NULL. Because no matching record is found in the dept table, the corresponding d.dname and d.loc columns are NULL.

Example 2: Perform a right outer join

Query for all departments and their employee information. The results must include departments that do not have any employees.

-- Use (+) to perform a right outer join. The (+) is placed on the column of the left table (emp).
SELECT e.ename, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
ORDER BY d.deptno, e.ename;

The query returns the following result:

 ename | sal  |   dname    |   loc    
-------+------+------------+----------
 CLARK | 2450 | ACCOUNTING | NEW YORK
 KING  | 5000 | ACCOUNTING | NEW YORK
 SMITH |  800 | RESEARCH   | DALLAS
 ALLEN | 1600 | SALES      | CHICAGO
       |      | OPERATIONS | BOSTON
(5 rows)

Result description:
The query results include all departments from the dept table. The department named OPERATIONS does not have any employees. Because no employees belong to this department, the corresponding e.ename and e.sal columns are NULL.

Example 3: Use in an aggregate query

The (+) operator can also be used in an aggregate query. For example, you can count the number of employees in each department, including departments that do not have any employees.

-- Count the number of employees in each department, including empty departments
SELECT d.dname, COUNT(e.empno) AS emp_count
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.dname, d.deptno
ORDER BY d.deptno;

Result:

   dname    | emp_count 
------------+-----------
 ACCOUNTING |         2
 RESEARCH   |         1
 SALES      |         1
 OPERATIONS |         0
(4 rows)

Result description:
For the OPERATIONS department, no matching employees are found. Therefore, the result of COUNT(e.empno) is 0.

Compatibility

Oracle 19c and later versions support using the (+) operator for outer joins on multiple tables in the WHERE clause. For example:

SELECT e.ename, d.dname, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno(+) = d.deptno AND e.sal(+) = s.hisal;

In PolarDB for PostgreSQL (Compatible with Oracle), this feature is supported differently depending on the revision version:

Revision versions earlier than 2.0.14.17.36.0: Not supported.
Revision version 2.0.14.17.36.0 or later: You can enable this feature by setting the polar_enable_transform_new_style_join_expr parameter.
Note
You can set this parameter at the session, user, or database level. Alternatively, you can configure it globally in a parameter template in the console.

FAQ

Why does my query return an ORA-01468 or ORA-01719 error?

This error usually occurs when the (+) operator is used incorrectly. Check your query for the following common errors:

Rule for the (+) operator: The (+) operator cannot be used on both sides of the equals sign in a join condition.
```
-- Incorrect example
SELECT * FROM emp e, dept d WHERE e.deptno(+) = d.deptno(+);
```
The use of the (+) operator in an OR condition is restricted: a condition that contains the (+) operator cannot be joined with other conditions using the OR operator.
```
-- Incorrect example
SELECT * FROM emp e, dept d WHERE (e.deptno = d.deptno(+) OR e.sal > 2000);
```