SQL 子查询请教

先看一个例子，有如下3张表。



CREATE TABLE course (

    cid BIGINT,

    cname VARCHAR(200)

);

CREATE TABLE student (

    sid BIGINT,

    sname VARCHAR(200)

);

CREATE TABLE student_course (

    scid BIGINT,

    sid BIGINT,

    cid BIGINT,

    score FLOAT

);

插入一些数据进去：

1,"english"
2,"math"
3,"computer"

1,"tom"
2,"john"
3,"jacky"
4,"mary"

1,1,1,90.0
2,1,2,80.0
3,1,3,80.0
4,2,3,70.0
5,2,2,60.0
6,2,1,70.0
7,3,1,75.0
8,3,2,85.0
9,3,3,95.0

如果要查询得到所有数学成绩大于平均分1分的学生的姓名，怎么查？首先想到的就是子查询：
1. 先找到数学成绩的平均分
2. 再找大学这个平均分1分的学生



SELECT S.*, SC.SCORE, C.CNAME

  FROM STUDENT AS S, STUDENT_COURSE AS SC, COURSE AS C

  WHERE S.SID = SC.SID AND C.CID = SC.CID AND C.CNAME = 'math'

    AND SC.SCORE > ((SELECT AVG(SC.SCORE)

    FROM STUDENT_COURSE AS SC, COURSE AS C

    WHERE C.CID = SC.CID AND C.CNAME = 'math') + 1)

结果如下：



      1 tom    80.0 math

      3 jacky  85.0 math

这个是最直接的办法，也是一种无关子查询，就是子查询的结果与外部的查询不相关。有什么更好的办法吗？
既然外部的查询应经有课程信息，子查询是不是可以利用？如下：



SELECT S.*, SC.SCORE, C.CNAME

  FROM STUDENT AS S, STUDENT_COURSE AS SC, COURSE AS C

  WHERE S.SID = SC.SID AND C.CID = SC.CID AND C.CNAME = 'math'

    AND SC.SCORE > ((SELECT AVG(SC.SCORE)

    FROM STUDENT_COURSE AS SC 

    WHERE C.CNAME = 'math') + 1)

这是一种相关子查询，也就是子查询用到了外部查询的信息，这样的查询性能会有问题。
这样写也得到了一样的结果，不过感觉怪怪的。谁能解释它怎么工作的吗？

网上查了说当有子查询的时候会先执行子查询在执行外部的查询。对于无关子查询，由于子查询的结果是确定的，与外面的查询无关，还好理解。但是对于相关子查询，由于子查询用到了外部查询的条件，在进行子查询的时候会遍历外部查询来确定子查询的结果，进而再去定外部查询的结果。不明白！

不好意思，sql写漏了

select s.sname
FROM student_course AS sc
JOIN student AS s ON sc.sid=s.sid
JOIN course AS c ON sc.cid=c.cid
JOIN (SELECT cid,avg(score) score FROM student_course GROUP BY cid) AS c_avg ON sc.cid=c_avg.cid
WHERE c.cname='math' AND sc.score > (c_avg.score+1)

select tt.sname from student_course ss,course cc,student tt
where ss.score >
(
select avg(sc.score)
from course c, student_course sc
where c.cid = sc.cid
and c.cname = 'math'
)
and cc.cname='math'
and ss.cid = cc.cid
and tt.sid = ss.sid;

个人比较喜欢下面的写法
把各科平均成绩查询出来作为一个表进行关联查询
子查询的性能一般不太好

SELECT s.sname
FROM student_course AS sc
JOIN student AS s ON sc.sid=s.sid
JOIN course AS c ON sc.cid=c.cid
JOIN (SELECT cid,avg(score) FROM student_course GROUP BY cid) AS c_avg ON sc.cid=c_avg.cid
WHERE c.cname='math' AND sc.score > (c_avg+1)

SQL 子查询 请教

SQL 子查询请教