Home » How to Use ALL in SQL

How to Use ALL in SQL

by Online Tutorials Library

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:

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:

  1. Create a database in the system.
  2. Create two new tables.
  3. Insert the data in both tables
  4. View the Inserted data of both tables
  5. 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:

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

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

Step 3: Insert the Values

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

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

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:

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:

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

Next TopicSQL Compare String

You may also like