How to Use ALL in SQL

In this SQL section, we will discuss how to use the 'ALL' keyword with the tables in Structured Query Language. To understand the examples of the 'ALL' keyword, we first have to know what it is.

What is Any in SQL?

The ALL is an operator in SQL. This operator compares the single record to every record of the list returned by the sub-query. This operator is always used with the SQL comparison operator, which is followed by the inner query.

The syntax for using ALL operator in Structured Query Language:

SELECT Column_Name_1, Column_Name_2, Column_Name_3, ……, Column_Name_N FROM Table_Name WHERE Column_Name Comparison_Operator ALL (SELECT Column_Name FROM Table_Name WHERE [condition]);

In the ALL syntax, the ALL operator is followed by the SQL comparison operator, which helps compare the column value with the sub-query.

We can use the following comparison operators with the ALL operator in the statements of SQL:

1. Equal operator (=)

This comparison operator with ALL operator evaluates to TRUE when the value of specified column is equal to any value in the returned list.

Syntax:

2. Not Equal operator (!=)

This comparison operator with the ALL operator evaluates to TRUE when the value of the specified column is not equal to any value of the returned list.

Syntax:

3. Greater Than operator (>)

This comparison operator with the ALL operator evaluates to TRUE when the value of the specified column is greater than the biggest value of the returned list.

Syntax:

4. Less Than operator (<)

This comparison operator with the ALL operator evaluates to TRUE when the value of the specified column is less than the smallest value of the returned list.

Syntax:

5. Greater Than Equal To operator (>=)

This comparison operator with the ALL operator evaluates to TRUE when the value of the specified column is greater than or equals the biggest value of the returned list.

Syntax:

6. Less Than Equals To operator (<=)

This comparison operator with the ALL operator evaluates to TRUE when the value of the specified column is less than or equals the smallest value of the returned list.

Syntax:

If you want to perform the 'ALL' operator in the tables of SQL, then you have to follow the below points one by one in the given manner:

Create a database in the system.
Create two new tables.
Insert the data in both tables
View the Inserted data of both tables
Use the ALL operator to view the data in different ways.

Now, we are going to explain the steps in detail with a SQL example:

Step 1: Create the Simple new database

Firstly, you have to make a new database in Structured Query Language. So, Let's start.

The following query creates the new University Database in SQL server:

Step 2: Create the New table

Now, use the below SQL syntax, which helps in creating the new table in the database:

CREATE TABLE table_name
(
column_Name_1 data type (character_size of the column_1),  
column_Name_2 data type (character_size of the column_2),  
column_Name_3 data type (character_size of the column_3),  
...  
column_Name_N data type (character_size of the column_N)
);  

The following query creates the Faculty_Info table in the University Database:

CREATE TABLE Faculty_Info
(
Faculty_ID INT NOT NULL PRIMARY KEY,  
Faculty_First_Name VARCHAR (100),  
Faculty_Last_Name VARCHAR (100),  
Faculty_Dept_Id INT NOT NULL,
Faculty_Address Varchar (80),
Faculty_City Varchar (80),
Faculty_Salary INT 
);

The following query creates the Department_Info table in the University Database:

CREATE TABLE Department_Info
(
Dept_Id INT NOT NULL,
Dept_Name Varchar(100),
Head_Id INT
);

Step 3: Insert the Values

The following INSERT queries insert the records of Faculty in the Faculty_Info table:

INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, Shivam, Gupta, 4001, 22 street, Kochi, 20000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, Bella, Devgan, 4002, 120 street, Kochi, 38000 );
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Gupta, 4001, 221 street, Mumbai, 45000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, Ramika, Singhania, 4001, 501 street, Jaipur, 42000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Gupta, 4002, 12 street, Delhi, 28000);
INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_Name Faculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary)VALUES (1007, Yadu, Besas, 4003, 202 street, Chandigarh, 35000);

The following INSERT queries insert the records of departments in the Department_Info table:

INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4001, Teaching, 1005);
INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4002, Account, 1009);
INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4003, 
Sports, 1007);

carat

Step 4: View the Table's Data

The following query shows the data of the Faculty_Info table.

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_Salary
1001	Shivam	Gupta	4001	22 Street	Kochi	20000
1002	Bella	Devgan	4002	120 Street	Kochi	38000
1004	Saurabh	Devgan	4001	221 Street	Mumbai	45000
1005	Ramika	Singhania	4001	501 Street	Jaipur	42000
1006	Avinash	Gupta	4002	12 Street	Delhi	28000
1007	Yadu	Besas	4003	202 Street	Chandigarh	35000

The following query shows the data of the Faculty_Info table.

Dept_Id	Dept_Name	Head_Id
4001	Teaching	1005
4002	Account	1009
4003	Sports	1007

Step 5: Use ALL operator to view the Table's data

The following query uses the ALL operator with an Equal comparison operator:

SELECT * FROM Faculty_Info WHERE Faculty_Id = ALL (SELECT Head_Id from Department_Info);

This query shows the details of that Faculty from the Faculty_Info table who is also the head of the Department_Info table.

The output of the above SELECT query with Equal operator is shown in the below table:

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_Salary
1005	Ramika	Singhania	4001	501 Street	Jaipur	42000
1007	Yadu	Besas	4003	202 Street	Chandigarh	35000

The following query uses ALL operator with less than operator and GROUP BY clause:

SELECT * FROM Faculty_Info WHERE Faculty_Salary < ALL (SELECT AVG ( Faculty_Salary ) from Faculty_Info GROUP BY Faculty_Dept_Id );

Firstly, you have to make a new database in Structured Query Language. So, Let's start.

The following query creates the new University Database in SQL server:

This query shows the details of all Faculties whose salaries are less than the average salary of every department.

The output of the above SELECT query with less than operator is shown in the below table:

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_Salary
1001	Shivam	Gupta	4001	22 Street	Kochi	20000
1006	Avinash	Gupta	4002	12 Street	Delhi	28000
1007	Yadu	Besas	4003	202 Street	Chandigarh	35000

The following query uses ALL operator with greater than operator and GROUP BY clause:

SELECT * FROM Faculty_Info WHERE Faculty_Salary > ALL (SELECT AVG ( Faculty_Salary ) from Faculty_Info GROUP BY Faculty_Dept_Id );

This query shows the details of all Faculties whose salaries are greater than the average salary of every department.

The output of the above SELECT query with the Greater Than operator is shown in the below table:

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Address	Faculty_City	Faculty_Salary
1001	Shivam	Gupta	4001	22 Street	Kochi	20000
1002	Bella	Devgan	4002	120 Street	Kochi	38000
1004	Saurabh	Devgan	4001	221 Street	Mumbai	45000
1005	Ramika	Singhania	4001	501 Street	Jaipur	42000
1006	Avinash	Gupta	4002	12 Street	Delhi	28000
1007	Yadu	Besas	4003	202 Street	Chandigarh	35000