Re: What replaces IN?

[Michael Stassen]

This will work, but there's no reason to use a LEFT JOIN here.  With a 
LEFT JOIN, you get a row for each item_id in table1 that does not have a 
corresponding item_id in table2, with the table2 fields set to NULL. 
You then have to filter these out with your WHERE clause.  Just use a 
simple join:

  SELECT table1.* FROM table1, table2
  WHERE table1.item_id=table2.item_id
That way, you only get rows for each item_id that exists in both tables, 
which was the point, with no need to filter the extra stuff the LEFT 
JOIN would have created.

Michael

Brent Baisley wrote:

IN is supported in 4.1, I'm not sure about 4.0. But any, you can do you 
query like this:

select table1.* from table1
left join table2 on table1.item_id=table2.item_id
where table2.item_id is not null
I think that's right. It may actually be quicker than using IN.

On Feb 6, 2004, at 2:20 PM, Scott Purcell wrote:

I am trying to run the below query, and believe that the 'IN' is not 
supported in mysql. What is the 'IN' replacement? I tried exists and 
that doesn't work.

select * from table1
    where item_id IN (select item_id from table2)



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]