sql之exists 用法

表 stu --------学生表（id,s_name）

表cla---------课程表（id,c_name）

表 stu_cla --------------学生选课表（id,stu_id,cla_id）

这种表关系其实存在需要业务功能，具体查询功能

1.返回没有选修的记录

SELECT     Stu.ID, Stu.s_name, Cla.ID AS Expr1, Cla.Cla_name
FROM         Stu CROSS JOIN
                      Cla
WHERE     (NOT EXISTS
                          (SELECT     ID, stu_id, cla_id
                            FROM          Stu_Cla

                            WHERE      (stu_id = Stu.ID) AND (cla_id = Cla.ID)))

2.返回学生id 为 2 没选修的课程

SELECT     ID, Cla_name
FROM         Cla
WHERE     (NOT EXISTS
                          (SELECT     cla_id
                            FROM          Stu_Cla
                            WHERE      (stu_id = 2) AND (cla_id = Cla.ID)))

SELECT     ID, Cla_name
FROM         Cla
WHERE     (ID NOT IN
                          (SELECT     cla_id
                            FROM          Stu_Cla
                            WHERE      (stu_id = 2)))

3.返回选修了所有课程的学生信息

  in 用法

SELECT     ID, s_name
FROM         Stu
WHERE     ID IN
              (SELECT     stu_id
                FROM       Stu_Cla
              GROUP BY stu_id
               HAVING      (COUNT(stu_id) =
                                            (SELECT     COUNT(*) AS Expr1
                                             FROM          Cla))

  exists用法  因为用exists，所以用exists，这个exists用得有点牵强

SELECT     ID, s_name
FROM         Stu
WHERE     EXISTS
                 (SELECT stu_id
                  FROM  (SELECT     stu_id
                          FROM   Stu_Cla
                           GROUP BY stu_id
                            HAVING      (COUNT(stu_id) =
                                         (SELECT     COUNT(*) AS Expr1
                                          FROM  Cla))) AS ss
                            WHERE   (stu_id = Stu.ID))



正规exists写法 /不存在有一门课程没选即 全选
SELECT     ID, s_name
FROM         Stu
WHERE     (NOT EXISTS
                          (SELECT     ID, Cla_name
                            FROM          Cla
                            WHERE      (NOT EXISTS
                                                       (SELECT     ID, stu_id, cla_id
                                                         FROM          Stu_Cla
                                                         WHERE      (Stu.ID = stu_id) AND (Cla.ID = cla_id)))))




4，所有学生都不选的课程信息

SELECT     ID, Cla_name
FROM         Cla
WHERE     (NOT EXISTS
                          (SELECT     ID, stu_id, cla_id
                            FROM          Stu_Cla
                            WHERE      (Cla.ID = cla_id)))
in用法


select * from cla where id not in(select distinct cla_id from stu_cla)