SQL--子查询

1、单行子查询

        select ename,deptno,sal
        from emp
        where deptno=(select deptno from dept where loc='NEW YORK')；

2、多行子查询

 SELECT ename,job,sal
        FROM EMP
        WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%')；

3、多列子查询

SELECT deptno,ename,job,sal
        FROM EMP
        WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno)；

4、内联视图子查询

(1)SELECT ename,job,sal,rownum
          FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)；
       (2)SELECT ename,job,sal,rownum
          FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
          WHERE rownum<=5；

5、在HAVING子句中使用子查询

SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN')； 

子查询优化：
表结构

DROP TABLE IF EXISTS `job`;
CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of job
-- ----------------------------
INSERT INTO `job` VALUES ('1', 'Java', '2019-03-18 23:08:44');
INSERT INTO `job` VALUES ('2', 'Python', '2019-03-18 23:08:55');
INSERT INTO `job` VALUES ('3', 'C++', '2019-03-18 23:09:02');

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '222');
INSERT INTO `user` VALUES ('2', '李四', '123');
INSERT INTO `user` VALUES ('3', '王五', '123');

-- ----------------------------
-- Table structure for `user_job`
-- ----------------------------
DROP TABLE IF EXISTS `user_job`;
CREATE TABLE `user_job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `job_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user_job
-- ----------------------------
INSERT INTO `user_job` VALUES ('1', '1', '1');
INSERT INTO `user_job` VALUES ('2', '1', '2');
INSERT INTO `user_job` VALUES ('3', '2', '1');
INSERT INTO `user_job` VALUES ('4', '3', '1');

EXPLAIN 
SELECT j.* 
FROM job AS j
WHERE j.id IN (
	SELECT job_id 
	FROM 
	user_job 
	WHERE user_id=1
)

EXPLAIN 
SELECT j.* 
FROM job AS j
INNER JOIN user_job AS uj ON j.id=uj.job_id
WHere uj.user_id=1

摘自：http://blog.csdn.net/devercn/article/details/22986/