Is there a way to emulate the functionality of this without a subquery:

SELECT Id, Name
  FROM Table1
 WHERE Table1.Id NOT IN 
       ( SELECT Id
         FROM Table2
         WHERE Flag=2 )

I know without the where clause on Table2, this can be done with an outer/left join... However, the where clause on the Flag is really confusing me.


    Requires Free Membership to View

    Login

Let's create some test data to try our queries on:

Table1             Table2
Id  Name           Id  Flag
91  Curly          91   2
92  Larry          92   1
93  Moe            93  null
94  Shemp
95  Joe  
96  Curly Joe

Notice that there is:

  • a matching row where Flag is 2 (Curly)
  • a matching row where Flag is not 2 (Larry)
  • a matching row where Flag is null (Moe)
  • several non-matching rows (Shemp, Joe, and Curly Joe)

Your original query has this subquery:

select Id
  from Table2 
 where Flag=2

This returns only 91 (Curly). Your main query therefore returns everybody except Curly. This what we will try to do without using a subquery.

Let's take a look at the approach you suggested, a LEFT OUTER join, but let's omit the Flag test initially:

select Table1.Id as Id1, Name
     , Table2.Id as Id2, Flag
  from Table1 
left outer
  join Table2
    on Table1.Id = Table2.Id 


Id1 Name       Id2 Flag
91 Curly       91   2
92 Larry       92   1
93 Moe         93  null
94 Shemp      null null
95 Joe        null null
96 Curly Joe  null null

Notice that columns from both Table1 and Table2 are in the SELECT list, so that we can see actual result set values as returned by the LEFT OUTER join. As you may know, in unmatched rows, columns from the right table are set to null. We can tell which rows had a match by looking at Id2, but not by looking at Flag, because Moe has a null stored in Table2, whereas Shemp, Joe, and Curly Joe don't.

Now let's add the Flag test to the ON condition:

select Table1.Id as Id1, Name
     , Table2.Id as Id2, Flag
  from Table1 
left outer
  join Table2
    on Table1.Id = Table2.Id 
   and             Flag <> 2 



Id1 Name       Id2 Flag
91 Curly      null null
92 Larry       92   1
93 Moe        null null
94 Shemp      null null
95 Joe        null null
96 Curly Joe  null null

Wait a second! How can this be? Why is Curly returned?

The answer is that in a LEFT OUTER join, each row of the left table must be returned, regardless of any ON conditions that may or may not bring in matching rows from the other table.

If this is confusing, let's walk through an example. Id1=91 (Curly) has a matching Id2, but its Flag is 2, so the ON conditions are not satisfied. But it's a LEFT OUTER Join, so Curly is returned anyway, with columns from Table2 set to null. Id2 is null whenever the ON conditions were not satisfied.

The solution is to move the Flag test to the WHERE clause, and allow for non-matching rows as well:

select Table1.Id as Id1, Name
     , Table2.Id as Id2, Flag
  from Table1 
left outer
  join Table2
    on Table1.Id = Table2.Id 
 where Flag is null
    or Flag <> 2


Id1 Name       Id2 Flag
92 Larry       92   1
93 Moe         93  null
94 Shemp      null null
95 Joe        null null
96 Curly Joe  null null

Now we have the results we want -- everybody except Curly -- without needing a subquery.

(This is for MySQL, right?)


This was first published in July 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top