Exclude set from left outer join

Question

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!

Answer 1

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

Answer 2

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

Answer 3

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')

Answer 4

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')

Answer 5

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

Answer 6

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.

Answer 7

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')