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,'2003-12-26','2003-12-26','08:05:38','08:05:38','0000-00-00 00:00:00','0000-00-00 00:00:00','',''),(2,5,5,'0000-00-00','0000-00-00','15:34:08','15:34:08','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),(3,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','2009-09-14 08:36:26','2009-09-14 08:36:26','',''),(4,6,6,'2006-03-07','2006-03-07','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','e','e'),(5,0,0,'2006-07-08','2006-07-08','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),(6,0,0,'2006-11-21','2006-11-21','23:58:52','23:58:52','2005-12-02 14:27:44','2005-12-02 14:27:44','',''),(7,3,3,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),(8,9,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','d','d'),(9,5,5,'2002-09-23','2002-09-23','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','w','w'),(10,0,0,'0000-00-00','0000-00-00','00:00:00','00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','z','z'); 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'); SELECT OUTR . `pk` AS X, OUTR . `varchar_nokey` FROM t1 AS OUTR WHERE OUTR . `varchar_nokey` IN ( SELECT INNR . `varchar_nokey` AS X FROM t2 AS INNR2 LEFT JOIN t2 AS INNR ON ( INNR2 . `int_nokey` = INNR . `pk` ) );