Help with Outer Join

[vbrich]

I am having problems figuring out how to accomplish something...

TABLE 1
> Field_ID (prm key)
> Name

TABLE 2
> ID (prm key)
> Field_ID (prm key)
> Stuff

I just want to return all the rows from TABLE 1 that DO NOT have a cooresponding match in TABLE 2.

For instance, this works... but is very slow. Is there a simple way to do this? I can't seem to figure out how to do a join that returns all rows from a table where the records do not match.

select * from TABLE1 where field_id NOT IN (select distinct field_id from TABLE2);

[ronverdonk]

Don't know if this is faster, but try it out

SELECT table1.id, table1.xx from table1
  LEFT JOIN table2 ON(table1.id=table1.id) 
  WHERE table2.id IS NULL;

Ronald :cool:

[vbrich]

Thanks for the post. It actually took twice as long that way. I might have to stick with the first query (about 30 seconds). I was just hoping there was an easier way.

[ronverdonk]

Have you ever tried to use the EXPLAIN option in your MySQL SELECT statement to see what table takes most of the time?

Ronald :cool:

[vbrich]

Nope. Never tried, but I don't do sql that often if you couldn't tell. I'll read up on it a little bit and compare these queries a bit more.

The primary reason for my post was to see if there was some sort of magical outer join that I was unaware of. I will play around with this a bit, but it sounds like it is going to be a large query no matter which way I slice it.

10k rows in table 1
20k rows in table 2 that reference the prm key from table 1
I need all the rows from table 1 that are not referenced in table2

Thanks