What Does a (+) Sign Mean in an Oracle SQL Where Clause

What does a (+) sign mean in an Oracle SQL WHERE clause?

This is an Oracle-specific notation for an outer join. It means that it will include all rows from t1, and use NULLS in the t0 columns if there is no corresponding row in t0.

In standard SQL one would write:

SELECT t0.foo, t1.bar
  FROM FIRST_TABLE t0
 RIGHT OUTER JOIN SECOND_TABLE t1;

Oracle recommends not to use those joins anymore if your version supports ANSI joins (LEFT/RIGHT JOIN) :

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions […]

Oracle: What does `(+)` do in a WHERE clause?

Depending on which side of the "=" the "(+) is on, it denotes a LEFT OUTER or a RIGHT OUTER join (in this case, it's a left outer join). It's old Oracle syntax that is sometimes preferred by people who learned it first, since they like that it makes their code shorter.

Best not to use it though, for readability's sake.

Oracle SQL: Meaning of (+)= in WHERE clause

The (+) identifies the table that is being outer joined to. The way I was taught, the (+) indicated the table that would have missing rows for which new NULL rows had to be added.

If you look at the alternate left outer join syntaxes that various databases supported before LEFT OUTER JOIN became part of the ANSI standard, the proprietary operator was generally applied to the table that was "missing" rows. DB2 also supports the (+) operator for outer joins in the same way that Oracle does.

Answer: Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join?

What is the meaning of '@' symbol in Oracle SQL?

It refers to a non-local table, the bit behind the @ is the db descriptor.

select * from question_answer@abcd where id = '45'

Means select not from the local question_answer table, but from the table on the db designated as abcd. The keyword to google for is dblink

What does the (+) operator mean in a where-clause of PL/SQL?

Yes, it means right join. if the statement was like .... where Tabel1.Attr (+) = Tabel2.Attr, it have to be left join.

What does the colon sign : do in a SQL query?

That's called a bind variable in Oracle.

what's the name for ":"?

Colon.

Oracle (+) Operator

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

This link is pretty good at explaining the difference between JOINs.

---

It should also be noted that even though the (+) works, Oracle recommends not using it:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

What does the symbol (+) mean at the end of a WHERE statement?

It could be use to do joins, but its not recommended:

Afaik, the + notation is only present for backwards compatibility because Oracle debuted it before the ANSI standard for joins was put in place. It's specific to Oracle and you should avoid using it in new code when there's an equivalent standards-compliant version available.

Edit: It seems there are differences between the two, and the + notation has restrictions that the ANSI join syntax does not have. Oracle themselves recommend that you not use the + notation.

From here: Difference between Oracle's plus (+) notation and ansi JOIN notation?

In Oracle, (+) denotes the "optional" table in the JOIN.

From here: Left Outer Join using + sign in Oracle 11g

Meaning of (+) in SQL queries

It's Oracle's synonym for OUTER JOIN.

SELECT *
FROM a, b
WHERE b.id(+) = a.id

gives same result as

SELECT *
FROM a
     LEFT OUTER JOIN b
     ON b.id = a.id

What does mean in Oracle

It means 'not equal to'. So you're filtering out records where ordid is 605. Overall you're looking for any records which have the same prodid and qty values as those assigned to ordid 605, but which are for a different order.

---

Related Topics