1. create table
CREATE TABLE `t1` (
`id` BIGINT(19) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(500) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
CREATE TABLE `t2` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`cid` INT(10) NULL DEFAULT NULL,
`uid` INT(10) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `cid` (`cid`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
2. testing
explain SELECT * FROM t1 WHERE id NOT IN(SELECT id FROM t2);
EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;