DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) NOT NULL, `int_key` int(11) NOT NULL, `date_key` date NOT NULL, `date_nokey` date NOT NULL, `time_key` time NOT NULL, `time_nokey` time NOT NULL, `datetime_key` datetime NOT NULL, `datetime_nokey` datetime NOT NULL, `varchar_key` varchar(1) NOT NULL, `varchar_nokey` varchar(1) NOT NULL, PRIMARY KEY (`pk`), KEY `int_key` (`int_key`), KEY `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`) ); INSERT INTO t1 VALUES (1,5,5,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-04-12 00:00:00','2009-04-12 00:00:00','o','o'); DROP TABLE IF EXISTS t2; CREATE TABLE t2 ( `pk` int(11) NOT NULL AUTO_INCREMENT, `int_nokey` int(11) NOT NULL, `int_key` int(11) NOT NULL, `date_key` date NOT NULL, `date_nokey` date NOT NULL, `time_key` time NOT NULL, `time_nokey` time NOT NULL, `datetime_key` datetime NOT NULL, `datetime_nokey` datetime NOT NULL, `varchar_key` varchar(1) NOT NULL, `varchar_nokey` varchar(1) NOT NULL, PRIMARY KEY (`pk`), KEY `int_key` (`int_key`), KEY `date_key` (`date_key`), KEY `time_key` (`time_key`), KEY `datetime_key` (`datetime_key`), KEY `varchar_key` (`varchar_key`) ); INSERT INTO t2 VALUES (1,5,5,'0000-00-00','0000-00-00','22:51:38','22:51:38','0000-00-00 00:00:00','0000-00-00 00:00:00','h','h'), (2,1,1,'2007-08-24','2007-08-24','07:26:48','07:26:48','2000-11-04 00:00:00','2000-11-04 00:00:00','{','{'), (3,1,1,'2009-04-12','2009-04-12','08:19:28','08:19:28','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'), (4,8,8,'2009-12-04','2009-12-04','00:00:00','00:00:00','2004-02-24 17:40:14','2004-02-24 17:40:14','x','x'), (5,7,7,'2000-09-17','2000-09-17','15:23:34','15:23:34','0000-00-00 00:00:00','0000-00-00 00:00:00','o','o'), (6,3,3,'0000-00-00','0000-00-00','08:52:49','08:52:49','2008-06-29 15:03:33','2008-06-29 15:03:33','p','p'), (7,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2005-04-06 00:00:00','2005-04-06 00:00:00','c','c'), (8,0,0,'0000-00-00','0000-00-00','15:10:28','15:10:28','2006-05-01 16:00:17','2006-05-01 16:00:17','k','k'), (9,6,6,'0000-00-00','0000-00-00','08:05:45','08:05:45','2002-09-17 00:00:00','2002-09-17 00:00:00','t','t'), (10,0,0,'0000-00-00','0000-00-00','19:46:39','19:46:39','0000-00-00 00:00:00','0000-00-00 00:00:00','c','c'); SET optimizer_switch='no_semijoin'; SELECT COUNT(varchar_key) AS X FROM t2 WHERE pk IN (SELECT pk FROM t1 ) AND pk = 8 HAVING X = NULL;