0

I'm going through some code and came across a view and was wondering what part of the WHERE statement was doing, it looks like so.

receipt_note.receipt_num(+) = receipt_data.receipt_num

receipt_num is a NUMBER in the table. I just don't know what the (+) would be doing here. Is it adding 1 to that number, like in coding where you would do variable++

user3178424
  • 37
  • 1
  • 7
  • 3
    This is the old deprecated way of writing an outer join in Oracle –  Jan 20 '14 at 14:55
  • 2
    Well it is old and Oracle recommends ANSI join syntax, but I don't think it is **deprecated** by Oracle and will be completly removed in future releases. If you personally prefer this syntax, you can use it. If you don't use the latest Oracle database release you are sometimes advised to go back to old Oracle syntax, because there were several bugs in Oracle related to ANSI join syntax. For example `FAST REFRESH` on Materialized Views was not possible if query was written with ANSI join syntax. I don't know if this bug has been fixed in the meantime, I did not test yet. – Wernfried Domscheit Jan 20 '14 at 15:09
  • I just checked Oracle page. ANSI join syntax is not possible for MATERIALIZED VIEW with FAST REFRESH. Oracle does not consider this as a bug! See Oracle Doc ID 1372720.1 for details, in case you have access t Oracle Support. – Wernfried Domscheit Jan 22 '14 at 11:46

1 Answers1

1

(+) is the (old) outer join operator in Oracle. It is specifying an outer join between the receipt_num columns of the receipt_note table and the receipt_data table.

This syntax is obsolete; new queries should use OUTER JOIN instead since it is more readable.

TypeIA
  • 16,916
  • 1
  • 38
  • 52
  • @Ben Had I been aware of any of them, I would have. Sorry to say I don't extensively dupe-check every question I know the answer to. I figure that's the poster's responsibility. Feel free to mark as duplicate yourself. Having over 20k rep you could be constructive rather than critical. – TypeIA Jan 20 '14 at 14:59
  • @Twinkles I did mention it was old, but you're right; edited answer accordingly. Thanks. – TypeIA Jan 20 '14 at 15:01
  • I did :-)... 20 seconds before I posted the comment. Sorry, didn't mean it to come across quite as bad as it did but I've seen this question so many times that I'm a bit jaded. – Ben Jan 20 '14 at 15:06
  • In this case what if there were repeats of numbers. Say receipt_note.receipt_num had 222 in it twice and receipt_data.receipt_num only had it once. Would this make 2 join records one for each repeated number? – user3178424 Jan 20 '14 at 15:06
  • @Ben OK. I'm not a regular on SQL questions (normally C#/C++) and I haven't seen this before. If I'm aware of a duplicate, or I come across one in researching an answer, I do post accordingly, but I don't have time to go out of my way to check for every answer. Luckily on a site this big *someone* is likely to catch it. – TypeIA Jan 20 '14 at 15:08
  • @user3178424 Yes, there would be two rows in the result, one for each row pairing. That's standard join behavior. "Outer" means that if there are no matching rows on the right side, the left side will be joined against an implicit NULL right row. – TypeIA Jan 20 '14 at 15:11