SET optimizer_switch = 'in_to_exists=on,exists_to_in=on';

CREATE TABLE t1 (a INT, b VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (4,'j'),(6,'v'),(3,'c');

CREATE TABLE t2 (c VARCHAR(1)) ENGINE=MyISAM;

INSERT INTO t2 VALUES ('b'),('y');

SELECT a FROM t1

WHERE EXISTS ( 

	SELECT 1 FROM t2 WHERE c = b 

) OR b NOT IN ('U')  

ORDER BY a;

bzr

a

---

4

6

3

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra

1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where

2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	

Warnings:

Note	1276	Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1

Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where (<expr_cache><`test`.`t1`.`b`>(<in_optimizer>(`test`.`t1`.`b`,`test`.`t1`.`b` in ( <materialize> (select `test`.`t2`.`c` from `test`.`t2` where 1 ), <primary_index_lookup>(`test`.`t1`.`b` in <temporary table> on distinct_key where ((`test`.`t1`.`b` = `<subquery2>`.`c`)))))) or (`test`.`t1`.`b` <> 'U')) order by `test`.`t1`.`a`

revision-id: sanja@montyprogram.com-20121004114534-4vclmqpxvhxkd7ok

date: 2012-10-04 14:45:34 +0300

build-date: 2012-10-05 23:05:07 +0400

revno: 3545