SQL All

SQL All

This tutorial covers SQL All operator.

The “all” operator is part of the logical operators.

It compares single column values with a scalar value. The syntax of “all” operator is the same as the “any” operator.

For more information on operators here.

Here we use two tables. First is the table “student_info” from the previous tutorials, and the second is a table named “grade“, linked to the first table with a foreign key.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

id	subjectName	grade	passed	studentID
1	History	C	1	1
2	Physics	A	1	2
3	History	F	0	3
4	Biology	A	1	NULL
5	Biology	E	1	5

All

The operator “all” functions as a comparison.

It returns TRUE if all of the conditions are met.

Below is an example.

SELECT firstName, lastName FROM student_info
WHERE id = ANY (SELECT studentID FROM grade WHERE grade = 'A');

firstName	lastname

The above statement outputs an empty outcome. The reason is that “all” requires all values to meet the condition. There are different grades, hence the empty result.

We can try a different approach to see the effect of the “all” operator.

Let’s select the first and last names of all students, if all ids after the second student’s id match the ids in the “grade” table.

SELECT firstName, lastName FROM student_info
WHERE id > ALL (SELECT studentID FROM grade WHERE studentID = 2);

firstName	lastname
Sam	Adams
Mike	Garcia
Patrick	Dumont

---

Next: SQL Like