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