SET SESSION optimizer_switch = 'semijoin=off';
CREATE TABLE `CC` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (8,NULL,'v','v');
INSERT INTO `CC` VALUES (9,'2006-06-14','r','r');
INSERT INTO `CC` VALUES (9,'2002-09-12','a','a');
INSERT INTO `CC` VALUES (186,'2005-02-15','m','m');
INSERT INTO `CC` VALUES (NULL,NULL,'y','y');
INSERT INTO `CC` VALUES (2,'2008-11-04','j','j');
INSERT INTO `CC` VALUES (3,'2004-09-04','d','d');
INSERT INTO `CC` VALUES (0,'2006-06-05','z','z');
INSERT INTO `CC` VALUES (133,'1900-01-01','e','e');
INSERT INTO `CC` VALUES (1,'1900-01-01','h','h');
INSERT INTO `CC` VALUES (8,'1900-01-01','b','b');
INSERT INTO `CC` VALUES (5,'2005-01-13','s','s');
INSERT INTO `CC` VALUES (5,'2006-05-21','e','e');
INSERT INTO `CC` VALUES (8,'2003-09-08','j','j');
INSERT INTO `CC` VALUES (6,'2006-12-23','e','e');
INSERT INTO `CC` VALUES (51,'2006-10-15','f','f');
INSERT INTO `CC` VALUES (4,'2005-04-06','v','v');
INSERT INTO `CC` VALUES (7,'2008-04-07','x','x');
INSERT INTO `CC` VALUES (6,'2006-10-10','m','m');
INSERT INTO `CC` VALUES (4,'1900-01-01','c','c');
CREATE TABLE `C` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,NULL,'w','w');
INSERT INTO `C` VALUES (9,'2001-09-19','m','m');
INSERT INTO `C` VALUES (3,'2004-09-12','m','m');
INSERT INTO `C` VALUES (9,NULL,'k','k');
INSERT INTO `C` VALUES (NULL,'2002-07-19','r','r');
INSERT INTO `C` VALUES (9,'2002-12-16','t','t');
INSERT INTO `C` VALUES (3,'2006-02-08','j','j');
INSERT INTO `C` VALUES (8,'2006-08-28','u','u');
INSERT INTO `C` VALUES (8,'2001-04-14','h','h');
INSERT INTO `C` VALUES (53,'2000-01-05','o','o');
INSERT INTO `C` VALUES (0,'2003-12-06',NULL,NULL);
INSERT INTO `C` VALUES (5,'1900-01-01','k','k');
INSERT INTO `C` VALUES (166,'2002-11-27','e','e');
INSERT INTO `C` VALUES (3,NULL,'n','n');
INSERT INTO `C` VALUES (0,'2003-05-27','t','t');
INSERT INTO `C` VALUES (1,'2005-05-03','c','c');
INSERT INTO `C` VALUES (9,'2001-04-18','m','m');
INSERT INTO `C` VALUES (5,'2005-12-27','y','y');
INSERT INTO `C` VALUES (6,'2004-08-20','f','f');
INSERT INTO `C` VALUES (2,'1900-01-01','d','d');
CREATE TABLE `BB` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_varchar_nokey` varchar(1) DEFAULT NULL,
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (8,'2002-02-21',NULL,NULL);
SELECT table2 .`col_date_key`
FROM BB table1 JOIN ( C table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
WHERE table1 .`col_varchar_nokey` IN (
SELECT `col_varchar_nokey`
FROM C ) ;
SELECT table2 .`col_date_key`
FROM BB table1 JOIN ( C table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_nokey` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key`
WHERE table1 .`col_varchar_nokey` IN (
'w', 'm', 'm', 'k', 'r', 't', 'j', 'u', 'h', 'o', NULL, 'k', 'e', 'n', 't', 'c', 'm', 'y', 'f', 'd'
);
Test case:
SET SESSION optimizer_switch = 'semijoin=off'; varchar_ nokey` varchar(1) DEFAULT NULL, key`,`col_ int_key` ) 06-14', 'r','r' ); 09-12', 'a','a' ); 02-15', 'm','m' ); 'y','y' ); 11-04', 'j','j' ); 09-04', 'd','d' ); 06-05', 'z','z' ); 01-01', 'e','e' ); 01-01', 'h','h' ); 01-01', 'b','b' ); 01-13', 's','s' ); 05-21', 'e','e' ); 09-08', 'j','j' ); 12-23', 'e','e' ); 10-15', 'f','f' ); 04-06', 'v','v' ); 04-07', 'x','x' ); 10-10', 'm','m' ); 01-01', 'c','c' ); varchar_ nokey` varchar(1) DEFAULT NULL, key`,`col_ int_key` ) 09-19', 'm','m' ); 09-12', 'm','m' ); 2002-07- 19','r' ,'r'); 12-16', 't','t' ); 02-08', 'j','j' ); 08-28', 'u','u' ); 04-14', 'h','h' ); 01-05', 'o','o' ); 12-06', NULL,NULL) ; 01-01', 'k','k' ); 11-27', 'e','e' ); 05-27', 't','t' ); 05-03', 'c','c' ); 04-18', 'm','m' ); 12-27', 'y','y' ); 08-20', 'f','f' ); 01-01', 'd','d' ); varchar_ nokey` varchar(1) DEFAULT NULL, key`,`col_ int_key` ) 02-21', NULL,NULL) ;
CREATE TABLE `CC` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (8,NULL,'v','v');
INSERT INTO `CC` VALUES (9,'2006-
INSERT INTO `CC` VALUES (9,'2002-
INSERT INTO `CC` VALUES (186,'2005-
INSERT INTO `CC` VALUES (NULL,NULL,
INSERT INTO `CC` VALUES (2,'2008-
INSERT INTO `CC` VALUES (3,'2004-
INSERT INTO `CC` VALUES (0,'2006-
INSERT INTO `CC` VALUES (133,'1900-
INSERT INTO `CC` VALUES (1,'1900-
INSERT INTO `CC` VALUES (8,'1900-
INSERT INTO `CC` VALUES (5,'2005-
INSERT INTO `CC` VALUES (5,'2006-
INSERT INTO `CC` VALUES (8,'2003-
INSERT INTO `CC` VALUES (6,'2006-
INSERT INTO `CC` VALUES (51,'2006-
INSERT INTO `CC` VALUES (4,'2005-
INSERT INTO `CC` VALUES (7,'2008-
INSERT INTO `CC` VALUES (6,'2006-
INSERT INTO `CC` VALUES (4,'1900-
CREATE TABLE `C` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (2,NULL,'w','w');
INSERT INTO `C` VALUES (9,'2001-
INSERT INTO `C` VALUES (3,'2004-
INSERT INTO `C` VALUES (9,NULL,'k','k');
INSERT INTO `C` VALUES (NULL,'
INSERT INTO `C` VALUES (9,'2002-
INSERT INTO `C` VALUES (3,'2006-
INSERT INTO `C` VALUES (8,'2006-
INSERT INTO `C` VALUES (8,'2001-
INSERT INTO `C` VALUES (53,'2000-
INSERT INTO `C` VALUES (0,'2003-
INSERT INTO `C` VALUES (5,'1900-
INSERT INTO `C` VALUES (166,'2002-
INSERT INTO `C` VALUES (3,NULL,'n','n');
INSERT INTO `C` VALUES (0,'2003-
INSERT INTO `C` VALUES (1,'2005-
INSERT INTO `C` VALUES (9,'2001-
INSERT INTO `C` VALUES (5,'2005-
INSERT INTO `C` VALUES (6,'2004-
INSERT INTO `C` VALUES (2,'1900-
CREATE TABLE `BB` (
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
`col_
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `BB` VALUES (8,'2002-
SELECT table2 .`col_date_key` nokey` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` nokey` IN (
FROM BB table1 JOIN ( C table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_
WHERE table1 .`col_varchar_
SELECT `col_varchar_nokey`
FROM C ) ;
SELECT table2 .`col_date_key` nokey` ) ON table3 .`col_varchar_key` = table2 .`col_varchar_key` nokey` IN (
FROM BB table1 JOIN ( C table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_
WHERE table1 .`col_varchar_
'w', 'm', 'm', 'k', 'r', 't', 'j', 'u', 'h', 'o', NULL, 'k', 'e', 'n', 't', 'c', 'm', 'y', 'f', 'd'
);