子查询的简单用法以及语句改写

子查询是一个 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);