Q

Outer join with a condition

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.


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close