Help with NON Join 3

[YOUNGCODGER]

Hi,

I have two tables that I extract data from by joining across two fields to form a unique identity. No problem here but I now want to check for entries that have no corresponding entry in the other table. How do I write this query? My initial attempt was a total failure and I got about 625,000,000 results out of tables with 10,000 to 20,000 entries each!

Many thnaks,

YoungCodger

 

[gmmastros]

Use a left join, like this...

Select Table1.*
From   Table1
       Left Join Table2
         On table1.Col1 = Table2.Col1
         And Table1.Col2 = Table2.Col2
Where  Table2.Col1 Is NULL

This will show you rows in table 1 that do not have a match in table2.

Make sense?




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[YOUNGCODGER]

Hi,

Did exactly what I wanted when I added a few more conditions in the where clause.

Many thanks,

YoungCodger

 

[gmmastros]

when I added a few more conditions in the where clause.

You need to be careful about adding conditions in a where clause when you use a left join. It can completely change the behavior. In fact, it can make a left join query act like an inner join.

I'm not saying what you did was wrong, I'm just saying to be careful. Let me show you an example.

[green]-- Setup some sample data[/green]
Declare @Table1 Table(Id Int, Fruit VarChar(20))

Insert into @Table1 Values(1, 'Apple')
Insert into @Table1 Values(2, 'Banana')
Insert into @Table1 Values(3, 'Grape')

Declare @Table2 Table(Id Int, Color VarChar(20))

Insert Into @Table2 Values(1, 'Red')
Insert Into @Table2 Values(2, 'Yellow')

[green]-- Left join returns 3 rows[/green]
Select *
From   @Table1 As Table1
       Left Join @Table2 As Table2
         On Table1.Id = Table2.Id

[green]-- Inner Join returns 2 rows.[/green]
Select *
From   @Table1 As Table1
       Inner Join @Table2 As Table2
         On Table1.Id = Table2.Id

[green]-- Left join with where clause on 'Right' table returns 1 row (acts like an inner join)[/green]
Select *
From   [!]@Table1[/!] As Table1
       [blue]Left[/blue] Join [!]@Table2[/!] As Table2
         On Table1.Id = Table2.Id
Where  Table2.Color = 'Red'

[green]-- Left join with condition in 'ON' clause continues to act like left join.[/green]
Select *
From   @Table1 As Table1
       Left Join @Table2 As Table2
         On Table1.Id = Table2.Id
         And Table2.Color = 'Red'

In the 3rd example, notice how table1 is the 'left' table and table2 is the 'right' table. When you put a where clause condition that uses the right table, the results are not as you would expect. You see, with a left join, you will get each row from the left table. If there is a corresponding row in the right table (in this example Table2), you will get it's values. If there is no match, the values will all be NULL. Now, when you put a where clause condition on the right table (like Color = 'Red'), the NULL values do not match and the row is filtered out. By putting the condition in the ON clause, you will get rows with the nulls.

I encourage you to copy paste the sample code. By understanding all 4 of the queries I presented, you will get a better understanding of left joins.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

George AWESOME explination and examples!!!!!!!!!!!!!