Home
Oracle's join operator (+) should not be used
Description
Oracle's join operator (+) should not be used as it is an outdated syntax that can lead to unexpected results. It is recommended to use the ANSI-standard JOIN syntax instead, which is more explicit and easier to read. Additionally, the ANSI-standard JOIN syntax is more efficient and can lead to better query performance.
Key Benefits
- No support for outer joins : Oracle's join operator (+) does not support outer joins, making it difficult to use when multiple tables need to be combined.
- No support for subqueries : Oracle's join operator (+) does not allow for subqueries, making it difficult to use when complex queries are needed.
- No support for multiple join types : Oracle's join operator (+) does not support multiple join types, such as left outer joins or right outer joins, making it difficult to use when more complex queries are needed.
- No support for table aliases : Oracle's join operator (+) does not support table aliases, making it difficult to use when multiple tables need to be combined.
Non-compliant Code Example
SELECT
customer_name,
od.order_id,
od.order_date,
od.total_amount
FROM
orders od, customers cust
WHERE
cust.id = od.CustomerId(+) --Non compliant code (Oracle's join operator (+) is used)
Compliant Code Example
SELECT
customer_name,
od.order_id,
od.order_date,
od.total_amount
FROM
orders od
LEFT OUTER JOIN customers cust ON cust.id = od.CustomerId; --Compliant code