CODEWITHSUNDEEP
sqloracle-databaseplsql
Dave Jarvis
Dave Jarvis

Reputation: 31191

Exclude set from left outer join

How do you exclude a set of values when using a left outer join?

Consider the following query:

SELECT i.id,
       i.location,
       area.description
  FROM incident_vw i,
       area_vw area
 WHERE i.area_code = area.code(+)
   AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')

The query executes, yet none of the NULL area code values appear.

BE AWARE: INCIDENT_VW.area_code can and does have NULL values.

Any ideas on how to match NULL incident area codes while excluding the given set of area codes, without using PL/SQL?

ANSI Update

Using the equivalent ANSI SQL also does not work:

    SELECT i.id,
           i.location,
           area.description
      FROM incident_vw i
 LEFT JOIN area_vw area
        ON area.code = i.area_code
     WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')

Solution

This works:

SELECT i.id,
       i.location,
       area.description
  FROM incident_vw i,
       area_vw area
 WHERE i.area_code = area.code(+)
   AND (i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P') and i.area_code IS NULL)

Thanks everyone!

Upvotes: 1

Views: 5311

Answers (7)

akf
akf

Reputation: 39495

The syntax looks correct. You should test your data to ensure your sanity. Try this:

SELECT count(*) 
FROM  incident_vw i
WHERE i.area_code NOT IN 
      (SELECT a.area_code 
       FROM area_vw a);

This will tell you if you have any incidents that don't have an area represented in the area table.

Upvotes: 0

van
van

Reputation: 77032

I am not sure I understand the question, but if you would like to rather get NULLs for the area codes in the exclude list, then just move your condition from WHERE to the JOIN condition:

    SELECT i.id,
           i.location,
           area.description
      FROM incident_vw i
 LEFT JOIN area_vw area
        ON area.code = i.area_code
       AND area.code NOT IN ('T20', 'V20B', 'V20O', 'V20P')

Upvotes: 0

Yannick Motton
Yannick Motton

Reputation: 36011

Seems the problem is the IN is removing all the area_codes that are NULL.

Give this a try:

    SELECT i.id,
           i.location,
           area.description
      FROM incident_vw i
 LEFT JOIN area_vw area
        ON area.code = i.area_code
     WHERE (i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
            OR i.area_code IS NULL)

Should give the desired result...

Upvotes: 4

bob
bob

Reputation: 417

I'm not 100% sure but maybe you are joining the l.area_code on the area.code when you should be joining the area_code on the l.area.code.

SELECT i.id,
       i.location,
       area.description
  FROM incident_vw i,
       area_vw area
 WHERE 1=1
   AND i.area_code = area.code(+)
   AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')

Should Be:

SELECT i.id,
       i.location,
       area.description
  FROM incident_vw i,
       area_vw area
 WHERE 1=1
   AND i.area_code(+) = area.code
   AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')

Upvotes: 0

hgmnz
hgmnz

Reputation: 13306

SELECT i.id,
       i.location,
       area.description
  FROM incident_vw i
  LEFT JOIN area_vw area
    ON i.area_code = area.code
  WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
UNION
SELECT i.id,
       i.location,
       NULL as description
   FROM incident_vw i
   WHERE i.area_code IS NULL

Upvotes: 0

Jeremy Bourque
Jeremy Bourque

Reputation: 3543

I think I would try this:

SELECT i.id,
       i.location,
       area.description
  FROM (SELECT *
          FROM incident_vw
         WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
            OR i.area_code IS NULL) i
     , area_vw area
 WHERE i.area_code = area.code (+)

Or the ANSI equivalent:

   SELECT i.id,
          i.location,
          area.description
     FROM (SELECT *
             FROM incident_vw
            WHERE i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P')
               OR i.area_code IS NULL) i
LEFT JOIN area_vw area
       ON i.area_code = area.code

Upvotes: 0

Ruffles
Ruffles

Reputation: 79

Could you try:

   SELECT i.id,
           i.location,
           area.description
      FROM incident_vw i
 LEFT JOIN area_vw area
        ON area.code = i.area_code
     WHERE NVL(i.area_code,'something') NOT IN ('T20', 'V20B', 'V20O', 'V20P')

Upvotes: 0

Related Questions