Use of OUTER JOIN AND OR operators together

Question

How to use the outer join and or operator together with the conditions mentioned below

Table 1: PERSON

ID_NO         NAME
512           RAM
513           RAVI
514           RAMU

Table 2: FORM_DETAILS

APPL_DATE      STATUS  ID NO 
12/09/2009       A       512
12/08/2010       A       513
12/07/2009       C       514
12/06/2006       S       513

The query

SELECT A.ID_NO,A.NAME B.APPL_DATE FROM PERSON A, FORM_DETAILS B
WHERE A.ID_NO=B.ID_NO(+) AND
B.APPL_DATE>='12/07/2009' AND
B.APPL_DATE<='12/09/2009' AND
B.STATUS='A' OR
B.STATUS='C'
ORDER BY APPL_DATE

Produces

Error: outer join operation(+) is not allowed in operand of or

Any suggestion is appreciated.

Is there supposed to be a - sign prior to 'C' or should this be an = sign? — Steve Matthews, Jul 29 '15 at 10:16
This is not an answer, but rather a comment on another possible issue. I'm guessing you mean _OR (B.STATUS='A' OR B.STATUS='C')_ as the brackets make this a single predicate. Otherwise this would mean meet all of the conditions before the _OR_, or only meet condition _B.STATUS='C'_. See why here - http://stackoverflow.com/a/1241158/201648. Gordon Linoff's use of _IN ('A', 'C')_ reflects this assumption. — Aaron Newton, Jul 30 '15 at 02:11

xlecoustillier · Answer 1 · 2015-07-29T10:21:34.260

Just change your query to (if you want to stick with the old (+) notation, discouraged, because it's vendor specific syntax, and deprecated):

SELECT A.ID_NO,
       A.NAME, 
       B.APPL_STATUS
  FROM PERSON A, 
       FORM_DETAILS B
    WHERE A.ID_NO = B.ID_NO(+) 
      AND B.APPL_DATE BETWEEN '12/07/2009' AND '12/09/2009'
      AND B.STATUS IN ('A', 'C')
ORDER BY APPL_DATE

Better, if you want to write ANSI compliant SQL:

SELECT A.ID_NO,
       A.NAME, 
       B.APPL_STATUS
  FROM PERSON A
  LEFT JOIN FORM_DETAILS B ON A.ID_NO = B.ID_NO
    WHERE B.APPL_DATE BETWEEN '12/07/2009' AND '12/09/2009'
      AND B.STATUS IN ('A', 'C')
ORDER BY APPL_DATE

Bulat · Accepted Answer · 2015-07-31T00:37:18.363

0

You should just use explicit join type instead of (+):

SELECT A.ID_NO,A.NAME B.APPL_STATUS 
FROM PERSON A LEFT JOIN FORM_DETAILS B ON A.ID_NO=B.ID_NO
WHERE
B.APPL_DATE BETWEEN '12/07/2009' AND '12/09/2009' AND
(B.STATUS = 'A' OR
B.STATUS = 'C')
ORDER BY APPL_DATE

edited Jul 31 '15 at 00:37

answered Jul 29 '15 at 10:14

Bulat

6,869
1
29
52

so how come you are accepting Gordon Linoff's answer that came 1 hour latter? – Bulat Jul 30 '15 at 09:42
I havent seen the time wen they all answered. I just pick the one in the 1st. anyways thanks for notifying. – test user Aug 03 '15 at 02:50

score 0 · Answer 3 · answered Jul 29 '15 at 10:17

Instead of a syntax which is deprecated for decades better use LEFT JOIN:

SELECT A.ID_NO,A.NAME B.APPL_STATUS FROM PERSON A
LEFT JOIN FORM_DETAILS B
ON A.ID_NO=B.ID_NO AND
B.APPL_DATE>='12/07/2009' AND
B.APPL_DATE<='12/09/2009' AND
B.STATUS='A' OR
B.STATUS='C'
ORDER BY APPL_DATE

score 0 · Answer 4 · answered Jul 29 '15 at 10:53

Forget the + syntax for joins. Learn to use explicit join syntax. It is much easier and compatible across databases.

You do need to be careful about where to put the conditions. I think you want:

SELECT P.ID_NO, P.NAME, FD.APPL_STATUS
FROM PERSON P LEFT JOIN
     FORM_DETAILS FD
     ON P.ID_NO = FD.ID_NO AND
        FD.APPL_DATE >= DATE '2009-12-07' and DATE '2009-12-09' AND
        FD.STATUS IN ('A', 'C')
ORDER BY FD.APPL_DATE;

Note the use of IN instead of OR. I'm pretty sure this has the right semantics for what you are trying to do.

Use of OUTER JOIN AND OR operators together

4 Answers4