sql之exists 用法_sql exists or exists-CSDN博客

本文链接：https://blog.csdn.net/liyanyun/article/details/6959207

本文介绍了一系列SQL查询技巧，包括如何查询学生未选修的课程、已选修所有课程的学生信息及所有学生都未选的课程等。通过不同SQL语句的应用，帮助读者掌握更复杂的查询方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表 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)