SQL - EXIST OR ALL?

sql any vs in
sql some
sql where all
difference between any and all in sql
sql select all
sql exists
sql case when exists
sql where = *

I have two different table student and grades; grade table has an attribute student_id which references student_id from student table. How do I find which student has every grade that exists?

If this is not clear,

Student     ID     Name
   1         1       John        
   2         2       Paul
   3         3       George
   4         4       Mike
   5         5       Lisa

Grade     Student_Id   Course   Grade
   1          1         Math       A
   2          1         English    B
   3          1         Physics    C
   4          2         Math       A
   5          2         English    A
   6          2         Physics    B
   7          3         Economics  A
   8          4         Art        C
   9          5         Biology    A

Assume there is only grade a,b,c (no d, e or fail) 

I want to find only John because He has grade a,b,c while other student like Paul(2) should not be selected because he does not have grade c. It does not matter which course he took, I just need to find if he has all the grades out there available.

Feel like I should something like exist or all function in sql but not sure. Please help. Thank you in advance.

I would use GROUP BY and HAVING, but like this:

SELECT s.Name
FROM Student s JOIN
     Grade g
     ON s.ID = g.Student_Id
GROUP BY s.id, s.Name
HAVING COUNT(DISTINCT g.Grade) = (SELECT COUNT(DISTINCT g2.grade) FROM grade g2);

You say "all the grades out there", so the query should not use a constant for that.

SQL ANY and ALL Operators, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java  Select Sql Courses Based On Your Individual Skill Level, Budget, And Schedule.

You can use HAVING COUNT(DISTINCT Grade) = 3 to check that the student has all 3 grades:

SELECT Name
FROM Student S
JOIN Grade G ON S.ID = G.Student_Id
GROUP BY Name
HAVING COUNT(DISTINCT Grade) = 3

Guessing at S.ID vs S.Student on the join. Not sure what the difference is there.

SQL Server EXISTS By Practical Examples, The following example returns all rows from the customers table: SELECT customer_id, first_name, last_name FROM sales.customers WHERE EXISTS  Search for Free Sql Databases on the New Internetcorkboard.com

By using exists

 select * from student s
  where exists ( select 1
                       from grades g where g.Student_Id=s.ID 
                        group by g.Student_Id
                        having count(distinct Grade)=3
               )  

Example

with Student as
(
select 1 as  id,'John' as person
union all
select 2 as  id,'Paul' as person
union all
select 3 as  id,'jorge'
),
Grades as
(
select 1 as Graden, 1 as Student_Id, 'Math' as Course, 'A' as Grade
union all
select 2 as Graden, 1 as Student_Id, 'English' as Course, 'B' as Grade 
union all
select 3 as Graden, 1 as Student_Id, 'Physics' as Course, 'C' as Grade
union all
select 4 as Graden, 2 as Student_Id, 'Math' as Course, 'A' as Grade 
union all
select 5 as Graden, 2 as Student_Id, 'English' as Course, 'A' as Grade 
union all
select 6 as Graden, 2 as Student_Id, 'Physics' as Course, 'B' as Grade 
)
select * from Student s
  where exists ( select 1
                       from Grades g where g.Student_Id=s.ID 
                        group by g.Student_Id
                        having count(distinct Grade)=3
               )

Note having count(distinct Grade)=3 i used this as in your sample data grade type is 3

SQL WHERE ANY | WHERE ALL, The SQL WHERE ANY and ALL syntax. The general ANY syntax is: SELECT column-names; FROM table-name  SQL ALL Example. The ALL operator returns TRUE if all of the subquery values meet the condition. The following SQL statement returns TRUE and lists the product names if ALL the records in the OrderDetails table has quantity = 10 (so, this example will return FALSE, because not ALL records in the OrderDetails table has quantity = 10):

Before delving into the answer, here's a working SQL Fiddle Example so you can see this in action.

As Gordon Linoff points out in his excellent answer, you should use GroupBy and Having Count(Distinct ... ) ... as an easy way to check.

However, I'd recommend changing your design to ensure that you have tables for each concern. Currently your Grade table holds each student's grade per course. So it's more of a StudentCourse table (i.e. it's the combination of student and course that's unique / gives you that table's natural key). You should have an actual Grade table to give you the list of available grades; e.g.

create table Grade
(
    Code char(1) not null constraint PK_Grade primary key clustered
)

insert Grade (Code) values ('A'),('B'),('C')

This then allows you to ensure that your query would still work if you decided to include grades D and E, without having to amend any code. It also ensures that you only have to query a small table to get the complete list of grades, rather than a potentially huge table; so will give better performance. Finally, it will also help you maintain good data; i.e. so you don't accidentally end up with students with grade X due to a typo; i.e. since the validation/constraints exist in the database.

select Name from Student s
where s.Id in 
(
    select sc.StudentId
    from StudentCourse sc
    group by sc.StudentId
    having count(distinct sc.Grade) = (select count(Code) from Grade)
)
order by s.Name

Likewise, it's sensible to create a Course table. In this case holding Ids for each course; since holding the full course name in your StudentCourse table (as we're now calling it) uses up a lot more space and again lacks validation / constraints. As such, I'd propose amending your database schema to look like this:

create table Grade
(
    Code char(1) not null constraint PK_Grade primary key clustered
)

insert Grade (Code) values ('A'),('B'),('C')

create table Course
(
    Id bigint not null identity(1,1) constraint PK_Course primary key clustered
    , Name nvarchar(128) not null constraint UK_Course_Name unique  
)

insert Course (Name) values ('Math'),('English'),('Physics'),('Economics'),('Art'),('Biology')

create table Student
(
    Id bigint not null identity(1,1) constraint PK_Student primary key clustered
    ,Name nvarchar(128) not null constraint UK_Student_Name unique  
)

set identity_insert Student on --inserting with IDs to ensure the ids of these students match data from your question

insert Student (Id, Name)
values (1, 'John')        
    ,  (2, 'Paul')
    ,  (3, 'George')
    ,  (4, 'Mike')
    ,  (5, 'Lisa')

set identity_insert Student off

create table StudentCourse
(
    Id bigint not null identity(1,1) constraint PK_StudentCourse primary key
    , StudentId bigint not null constraint FK_StudentCourse_StudentId foreign key references Student(Id)
    , CourseId bigint not null constraint FK_StudentCourse_CourseId foreign key references Course(Id)
    , Grade char /* allow null in case we use this table for pre-results; otherwise make non-null */ constraint FK_StudentCourse_Grade foreign key references Grade(Code)
    , Constraint UK_StudentCourse_StudentAndCourse unique clustered (StudentId, CourseId)
)

insert StudentCourse (StudentId, CourseId, Grade)
select s.Id, c.Id, x.Grade
from (values
   ('John',       'Math',     'A')
  ,('John',       'English',  'B')
  ,('John',       'Physics',  'C')
  ,('Paul',       'Math',     'A')
  ,('Paul',       'English',  'A')
  ,('Paul',       'Physics',  'B')
  ,('George',     'Economics','A')
  ,('Mike',       'Art',      'C')
  ,('Lisa',       'Biology',  'A')
) x(Student, Course, Grade)
inner join Student s on s.Name = x.Student
inner join Course c on c.Name = x.Course

SQL, ALL operator is used to select all tuples of SELECT STATEMENT. It is also used to compare a value to every value in another value set or result from a subquery. EXISTS (Transact-SQL) 03/15/2017; 5 minutes to read; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . Specifies a subquery to test for the existence of rows. Transact-SQL Syntax Conventions. Syntax EXISTS ( subquery ) Arguments. subquery Is a restricted SELECT statement.

SQL, Delete the record of all the customer from Order Table whose last name is 'Mehra'​. DELETE FROM Orders WHERE EXISTS (SELECT * FROM customers WHERE  I have two different table student and grades; grade table has an attribute student_id which references student_id from student table. How do I find which student has every grade that exists? If

SQL All operator, ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list or results from a query. The ALL must be  ANY and ALL are all SQL logical operators used in conjunction with sub-query. For explanation let us consider a table customers customers(Name, Age, Salary) This

SQL: EXISTS Condition, This SQL tutorial explains how to use the SQL EXISTS condition with syntax and This website would not exist without the advertisements we display and your kind Now let's find all of the records from the customers table where there is at​  The EXISTS operator is a logical operator that allows you to check whether a subquery returns any row. The EXISTS operator returns TRUE if the subquery returns one or more row. The following shows the syntax of the SQL Server EXISTS operator: EXISTS ( subquery) In this syntax, the subquery is a SELECT statement only.

Comments
  • Tag your question with the DBMS you are using (ie SQL Server, Oracle, MySQL, etc)
  • Also, should the first column in the grade table be GradeID? Right now there are two "Grade" columns...