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
Visual Expert 2024
 VEPLSQLRULE114