Situation
When executing the following queries, the same number of records is obtained, I would like to know what is the difference of using a (+)
at the end of the query.
SELECT A.IDTABLAA,B.NOMBRETABLAB
FROM TABLA_A A, TABLA_B B
WHERE A.IDTABLAA=B.IDTABLAB
--Record Count: 2,265
SELECT A.IDTABLAA, B.NOMBRETABLAB
FROM TABLA_A A, TABLA_B B
WHERE A.IDTABLAA=B.IDTABLAB(+)
--Record Count: 2,265
Environment
- Database: Oracle 11g
The operator
(+)
indicates that an OUTER JOIN will be done, depending on the position of the operator. An OUTER JOIN returns all rows that meet the specified condition, plus 0 or more rows from one table that do not have a match in the other table.To do a
LEFT OUTER JOIN
, the operator must appear in the table on the right side:In the specific case of your tables, a is being performed
LEFT OUTER JOIN
, so the result will be all the rows that meet the condition ofJOIN
and also the rowsA.IDTABLAA
that have no match.To do a
RIGHT OUTER JOIN
, the operator must appear in the table on the left side:The following restrictions must also be considered:
(*)
within the clauseWHERE
JOIN
, the operator must be used on allJOIN
.WHERE
If appears in the clause(*)
, it cannot be combined with other conditions with theAND
or operatorsOR
.This is an operator unique to Oracle, so if you wanted to port this code to some other RDBMS it is recommended to use the standard syntax of
OUTER JOINS
.That's the old Oracle syntax from
LEFT OUTER JOIN
before it was standardizedThe equivalent with the current syntax would be:
The difference is that with the
LEFT OUTER JOIN
( or (+) to the right of=
the in theWHERE
) all the rows of the table are included in the resultA
even though they have no correspondence in the table ,B
placing aNULL
as a value in that case.