SQL joins using (+) operator in where clause

Question

I am trying to rewrite an Oracle SQL query that uses operators in the where clause to join in the from clause instead. I understand how to tell if left or right join, but I don't know what to make of this... Is this referencing a column number???

 where alias.column(+) = 1

what is this trying to tell me? I'm to simply rewrite to something like:

 left join table
 on alias.column = 1

Is one an actual value? If so then why use the (+)? I'm not familiar with SQL so sorry if this is a basic question. It's not an easy thing to google either... I've tried for last hour and cannot find anything to explain this join...

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

Take a look at this: " (+) = " operator in oracle sql in where clause (+) is an old Oracle Syntax used for joins, but it's deprecated. And yes, you can (and should) rewrite it like this.

edited May 23 '17 at 11:43

Community

1
1

answered Jul 09 '15 at 05:23

Thomas

126
1
8

score 1 · Answer 2 · answered Jul 09 '15 at 06:07

1

It is actually useful when the data is null for some fields and Still you want to print all the data. Please find the SQL fiddle here for better understanding :

In this, first query won't give you any result. But the second query with (+) operator will give all the rows with null, if the data is empty.

answered Jul 09 '15 at 06:07

Arun Palanisamy

5,281
6
28
53

This is the correct answer. The (+) makes the join and "outer join" meaning that it will try to match with 1 if there is a non-null value in alias.column. – Uphill_ What '1 Jul 09 '15 at 06:29

score 0 · Answer 3 · answered Jul 08 '15 at 22:10

I don't have a source for this answer, but in my testing it looks like the (+) is doing nothing when compared to a literal. The following two queries return the same empty set.

WITH table1 AS
 (SELECT LEVEL num_col FROM dual CONNECT BY LEVEL <= 100)
,    table2 AS
 (SELECT LEVEL * 2 num_col FROM dual CONNECT BY LEVEL <= 50)
SELECT *
  FROM table1 a_tab
      ,table2 b_tab
 WHERE b_tab.num_col(+) = 1
 ORDER BY 1 DESC;

WITH table1 AS
 (SELECT LEVEL num_col FROM dual CONNECT BY LEVEL <= 100)
,    table2 AS
 (SELECT LEVEL * 2 num_col FROM dual CONNECT BY LEVEL <= 50)
SELECT *
  FROM table1 a_tab
      ,table2 b_tab
 WHERE b_tab.num_col = 1
 ORDER BY 1 DESC;

However, putting the symbol in a comparison against a column produces the left join as expected

WITH table1 AS
 (SELECT LEVEL num_col FROM dual CONNECT BY LEVEL <= 100)
,    table2 AS
 (SELECT LEVEL * 2 num_col FROM dual CONNECT BY LEVEL <= 50)
SELECT *
  FROM table1 a_tab
      ,table2 b_tab
 WHERE b_tab.num_col(+) = a_tab.num_col
 ORDER BY 1 DESC;

and putting in a value produces a cross join

WITH table1 AS
 (SELECT LEVEL num_col FROM dual CONNECT BY LEVEL <= 100)
,    table2 AS
 (SELECT LEVEL * 2 num_col FROM dual CONNECT BY LEVEL <= 50)
SELECT *
  FROM table1 a_tab
      ,table2 b_tab
 WHERE b_tab.num_col(+) = 2
 ORDER BY 1 DESC;

I am working in 11g r2 though, so that may not be the same on your version. I'd say the best solution is to compare data sets to make sure you still return what you expect from the previous query.

SQL joins using (+) operator in where clause

3 Answers3