CODEWITHSUNDEEP
sqloracle-databasejoinoperators
user3486773
user3486773

Reputation: 1246

SQL joins using (+) operator in where clause

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...

Upvotes: 0

Views: 117

Answers (3)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

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.

Upvotes: 1

Thomas
Thomas

Reputation: 126

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.

Upvotes: 1

Chris Hep
Chris Hep

Reputation: 1141

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.

Upvotes: 0

Related Questions