Oracle SQL conditional join

Question

I have the following SQL statement:

select
    DOCUMENT.DOCUMENT_ID,
    (case
        when DOCUMENT.CLASSIFICATION_CODE is not null
        then DOCUMENT.CLASSIFICATION_CODE
        else TEMPLATE.CLASSIFICATION_CODE end) CLASSIFICATION_CODE,
    CLASSIFICATION.NAME CLASSIFICATION_NAME
from
    DOCUMENT,
    TEMPLATE,
    CLASSIFICATION
where
    DOCUMENT.TEMPLATE_ID = TEMPLATE.TEMPLATE_ID and
    DOCUMENT.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+)

What I would like to do is along the lines of:

if DOCUMENT.CLASSIFICATION_CODE IS NOT NULL THEN 
  DOCUMENT.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+),
ELSE
  TEMPLATE.CLASSIFICATION_CODE = CLASSIFICATION.CLASSIFICATION_CODE(+)

Is this possible?

Learn to use proper explicit JOIN syntax. Who can remember what (+) means on a WHERE clause? — Gordon Linoff
– Gordon Linoff, Commented Apr 25, 2016 at 18:12
UGH! Non-ANSI outer join syntax? In 2016!?!?! What is this world coming to! — ErikE
– ErikE, Commented Apr 25, 2016 at 18:15

trincot · Accepted Answer · 2016-04-25 18:19:26Z

4

You can use NVL or the more standard COALESCE for that:

COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE)
      = CLASSIFICATION.CLASSIFICATION_CODE(+)

In ANSI syntax:

select
    DOCUMENT.DOCUMENT_ID,
    COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE) 
        CLASSIFICATION_CODE,
    CLASSIFICATION.NAME CLASSIFICATION_NAME
from
    DOCUMENT
inner join 
    TEMPLATE on DOCUMENT.TEMPLATE_ID = TEMPLATE.TEMPLATE_ID
left join
    CLASSIFICATION on
         COALESCE(DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE) 
       = CLASSIFICATION.CLASSIFICATION_CODE

edited Apr 25, 2016 at 18:19

answered Apr 25, 2016 at 18:09

trincot

357k38 gold badges282 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Allan · Accepted Answer · 2016-04-25 18:58:29Z

3

While you can use coalesce or nvl, as other answers have suggested, I'd prefer to join to the classification table twice. Yes, the engine has to perform an extra join, but avoiding the functions allows the engine to use an index.

SELECT document.document_id,
       COALESCE (d.classification_code, t.classification_code)
          AS classification_code,
       COALESCE (d_c.name, t_c.name) AS classification_name
FROM   document d
       JOIN template t ON d.template_id = t.template_id
       LEFT JOIN classification d_c
          ON d.classification_code = d_c.classification_code
       LEFT JOIN classification t_c
          ON t.classification_code = t_c.classification_code

answered Apr 25, 2016 at 18:58

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Comments

MT0 · Accepted Answer · 2016-04-25 18:29:33Z

1

You can use:

NVL( DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE )
COALESCE( DOCUMENT.CLASSIFICATION_CODE, TEMPLATE.CLASSIFICATION_CODE )
or CASE WHEN DOCUMENT.CLASSIFICATION_CODE IS NOT NULL THEN DOCUMENT.CLASSIFICATION_CODE ELSE TEMPLATE.CLASSIFICATION_CODE END (exactly as you used in the SELECT clause)

As the join condition, like this:

SELECT d.DOCUMENT_ID,
       NVL( d.CLASSIFICATION_CODE, t.CLASSIFICATION_CODE )
         AS CLASSIFICATION_CODE,
       c.NAME AS CLASSIFICATION_NAME
FROM   DOCUMENT d
       INNER JOIN TEMPLATE t
       ON ( d.TEMPLATE_ID = t.TEMPLATE_ID )
       LEFT OUTER JOIN CLASSIFICATION c
       ON ( NVL( d.CLASSIFICATION_CODE, t.CLASSIFICATION_CODE )
              = c.CLASSIFICATION_CODE )

edited Apr 25, 2016 at 18:29

answered Apr 25, 2016 at 18:24

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

Oracle SQL conditional join

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related