Exclusive records between tables

[tbode2006]

Given two tables. The first is a list of 100 words.
The second contains up to 20 words, constantly changing. At any time I would like to know what words exist in the second table that are NOT in the first table. I have been playing with the different type of Joins but without success in getting the ones that are NOT in the large table. I can get the ones in common. What am I missing?

 

[monksnake]

This won't use an index, but for a table that small, it shouldn't matter:

Select col1 from table2 where col1 not in (select col1 from table1)

table1 is the 100 word table

table2 is the 20 word table

<.

 

[gmmastros]

What is in Table2 But Not in Table1...

[COLOR=blue]Select[/color] Table2.Word
[COLOR=blue]From[/color]   Table2
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] Table1
         [COLOR=blue]On[/color] Table2.Word = Table1.Word
[COLOR=blue]Where[/color]  Table1.Word [COLOR=blue]Is[/color] NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tbode2006]

Thanks for the quick reply

 

[tbode2006]

Now, I want to delete the table 1 records that are not in table 2 and am having trouble with the syntax.
Any suggestions?

 

[SQLSister]

Delete t2
From   Table2 t2
       Left Join Table1 t1
         On T2.Word = T1.Word
Where  T1.Word Is NULL

As always with a delete statment, test to make sure it is doing what you want before running on production data.

"NOTHING is more important in a database than integrity." ESquared