Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Found the following in an Oracle-based application that we're migrating (generalized):

SELECT     Table1.Category1,     Table1.Category2,     count(*) as Total,     count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2 

What does (+) do in a WHERE clause? I've never seen it used like that before.

like image 375
Jonathan Lonowski Avatar asked Jan 10 '09 00:01

Jonathan Lonowski


People also ask

What is WHERE clause in Oracle?

The Oracle WHERE Clause is used to restrict the rows returned from a query. While the previous chapter (Basic SELECT Statements) explained how to extract the names of all customers from Customers table, using the Oracle WHERE clause, you are able to restrict the query to rows that meet a certain condition.

What does (+) mean in Oracle query?

The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.

Why do we add (+) in SQL?

You use this to assure that the table you're joining doesn't reduce the amount of records returned.

What is WHERE clause in PL SQL?

The Oracle WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.


1 Answers

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.

like image 61
SquareCog Avatar answered Oct 01 '22 02:10

SquareCog