子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。
任何允许使用表达式的地方都可以使用子查询，子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。
相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`account` VARCHAR(100) DEFAULT NULL,
`password` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `account` (`account`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `st_session_key` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT(10) UNSIGNED NOT NULL,
`session_key` VARCHAR(200) NOT NULL,
`created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
insert into `user` (`id`, `account`, `password`) values('1','zhangsan','123');
insert into `user` (`id`, `account`, `password`) values('2','lisi','123');
insert into `user` (`id`, `account`, `password`) values('3','wangwu','123');
insert into `user` (`id`, `account`, `password`) values('4','kety','123');
insert into `user` (`id`, `account`, `password`) values('5','marh','123');
insert into `user` (`id`, `account`, `password`) values('6','kimmy','123');
insert into `user` (`id`, `account`, `password`) values('7','tom','123');
insert into `user` (`id`, `account`, `password`) values('8','jimmy','123');
insert into `user` (`id`, `account`, `password`) values('9','sunny','123');
insert into `st_session_key` (`id`, `user_id`, `session_key`, `created_time`) values('1','1','aaaaaaa','2017-03-02 09:30:53');
insert into `st_session_key` (`id`, `user_id`, `session_key`, `created_time`) values('2','3','aaaaatrrr','2017-03-02 09:31:03');
insert into `st_session_key` (`id`, `user_id`, `session_key`, `created_time`) values('3','2','rrfvgukmm','2017-03-02 09:31:08');
insert into `st_session_key` (`id`, `user_id`, `session_key`, `created_time`) values('4','6','oijnhh','2017-03-02 09:31:15');
-
NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法#####
-
not in######
SELECT account FROM USER WHERE id NOT IN (SELECT user_id FROM st_session_key WHERE session_key LIKE '%aaaa%');
-- 语句改写
SELECT u.account FROM USER u LEFT JOIN st_session_key ssk ON ssk.user_id=u.id AND session_key LIKE '%aaaa%' WHERE ssk.id IS NULL;
-
not exists######
SELECT id,account FROM USER WHERE NOT EXISTS(SELECT user_id FROM st_session_key WHERE user_id = user.id);
-- 语句改写
SELECT u.id,u.account FROM USER u LEFT JOIN st_session_key ssk ON u.id=ssk.user_id WHERE ssk.id IS NULL;
-
如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替#####
SELECT account FROM USER WHERE id IN (SELECT user_id FROM st_session_key WHERE session_key LIKE '%aaaa%');
-- 语句改写
SELECT u.account FROM USER u INNER JOIN st_session_key ssk ON u.id=ssk.user_id AND session_key LIKE '%aaaa%';
-
IN的相关子查询用EXISTS代替#####
SELECT account FROM USER WHERE id IN (SELECT user_id FROM st_session_key WHERE session_key LIKE '%aaaa%');
-- 语句改写
SELECT account FROM USER WHERE EXISTS (SELECT user_id FROM st_session_key WHERE session_key LIKE '%aaaa%' AND user.id = user_id);
