Implementation of TSQL "match all" semantics

In TSQL, there is a exists clause that indicates that there is a match on any row of data, but how to represent the entire row of data. For example, it means that a student took all courses, which is a classic example of the "match all" semantics.

example, get the name of the student "take all " course

1, create instance data

--CREATE TABLECreate Tabledbo.student (Student_noint, Student_namevarchar(Ten))Create TableDbo.course (Course_noint, Course_namevarchar(Ten))Create TableDbo.student_course (Student_noint, Course_noint)--Insert DataInsert  intodbo.student (student_no,student_name)Values(1,'stu_a'),(2,'Stu_b'),(3,'Stu_c'),(4,'Stu_d')Insert  intodbo.course (course_no,course_name)Values( One,'中文版'),( A,'Math'),( -,'Chinese')Insert  intodbo.student_course (student_no,course_no)Values(1, One),(1, A),(1, -),(2, One),(2, -),(3, One),(3, A),(4, A)

2, "double negation" into affirmation

exists means matching any one, using two not exists: there is no one course the student does not have an elective

SelectS.student_name fromdbo.student Swhere  not exists (    Select NULL     fromDbo.course Cwhere  not EXISTS    (        Select NULL         fromDbo.student_course SCwhereSc.student_no=S.student_no andSc.course_no=c.course_no))

3, use the GROUP BY clause to implement

Group the selected timetable according to the school number

Declare @CouseCount intSelect @CouseCount=Count(0) fromDbo.course; withCte as (SelectSc.student_no fromDbo.student_course SCInner JoinDbo.course C onSc.course_no=C.course_noGroup  bySc.student_no having Count(0)=@CouseCount)SelectS.student_name fromdbo.student SInner JoinCTE C onS.student_no=C.student_no

Implementation of TSQL "match all" semantics