Oracle "(+)" Operator

Oracle "(+)" Operator

In Oracle, the "(+)" operator is used in a SQL query to indicate a "left outer join" or "left join" between two tables. The basic syntax of the query is as follows:

SELECT column1, column2, ...
FROM table1, table2
WHERE table1.column = table2.column(+)

  • "column1, column2, ..." is the list of columns you want to select.
  • "table1" and "table2" are the names of the tables you want to join.
  • "table1.column = table2.column" is the join condition.

For example, to join the "employees" table and the "departments" table on the "department_id" column, you can use the following query:

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id(+);

This query will return all employees' last name, department name, and department name, even if there is no matching employee for a department. Please note that the "(+)" operator is deprecated in Oracle 12c and later versions; instead you should use the ANSI SQL join syntax. The same example query using ANSI SQL join syntax would be:

SELECT last_name, department_name
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id;

This query will return the same results as the previous query using the (+) operator, but different databases and more readable more widely support it.

Posting Komentar

0 Komentar