Ferhat BINGOL wrote:
SELECT
a.address
FROM
a,
b
WHERE
(a.id LIKE b.id) AND
(b.message NOT LIKE 'y')
This won't work. That is, it's not equivalent to the original subquery version, which returns all the rows (ids) in a that do not have message='y' anywhere in b. The above query gets the rows in a that have a corresponding row in b with a non-'y' value in message. That's not the same thing. For example, an id which exists in a but not in b should be in the results, but wouldn't be included in the results of the query above. Also, if b were to have 2 rows with the same id, but different values of message, one 'y', one something else, then we wouldn't want that id, but the above query would include that id.
I should also point out that it isn't a good idea to use 'LIKE' when you mean '='. LIKE is for pattern matching. If your pattern is a constant (no wildcards), then you don't need LIKE (though it shouldn't hurt). That is,
message NOT LIKE 'y'
is the same thing as
message != 'y'
Now, for strings, that's no big deal, but it leads to
a.id LIKE b.id
where a.id and b.id are surely integers. LIKE is a string comparison function, so the numbers in a.id and b.id must be converted to strings before LIKE can compare them. That is undesirable, as it prevents the use of an index to find the match. For example,
mysql> explain SELECT * FROM inits WHERE id = 44000; +-------+-------+---------------+---------+---------+-------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+---------+---------+-------+------+-------+ | inits | const | PRIMARY | PRIMARY | 4 | const | 1 | | +-------+-------+---------------+---------+---------+-------+------+-------+ 1 row in set (0.01 sec)
mysql> explain SELECT * FROM inits WHERE id LIKE 44000; +-------+------+---------------+------+---------+------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+-------+-------------+ | inits | ALL | PRIMARY | NULL | NULL | NULL | 50000 | Using where | +-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.01 sec)
Notice that in the first case (WHERE id = 44000), the index on id will be used to find the single matching row, but in the second case (WHERE id LIKE 44000), no index will be used. Instead, mysql will do a full table scan, converting the ids to strings to be compared. Ouch.
Michael
-----Original Message----- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Sunday, January 30, 2005 7:19 PM To: mysql Cc: Harish Subject: Re: Need a query to get the difference of two tables
Harish wrote:
Hi,
I apprecaite anybody replying me with an equvalent query for this: I am using mysql 4.0.21
select a.address from a where a.id not in (select b.iid from b where b.message='y')
This can be done with a left join:
select a.address from a left join b on b.iid=a.id where b.iid is null;
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
