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

sql oracle
Steve Perkins picture Steve Perkins · Nov 18, 2010 · Viewed 120.2k times · Source

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

Consider the simplified SQL query below, in an Oracle database environment (although I'm not sure that it's Oracle-specific):

SELECT 
   t0.foo, t1.bar
FROM
   FIRST_TABLE t0, SECOND_TABLE t1
WHERE
   t0.ID (+) = t1.ID;

What is that (+) notation for in the WHERE clause? I'm sorry if this is an ignorant newbie question, but it's been extremely difficult to search for on Google or StackOverflow... because even when using quote marks, search engines see a '+' sign and seem to want to treat it as some kind of a logical directive.

Answer

Benoit picture Benoit · Nov 18, 2010

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 […]

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More