SQL ALL Operator

In sql, ALL operator is used to return true when the given value matches with all the values in single column set of values. The ALL operator in sql is like an AND operator and it will compare a value against with all values in column.

Syntax of SQL ALL Operator

Following is the syntax of defining an ALL operator in sql server.

 

SELECT column1, column2 FROM tablename WHERE column1 = ALL(SELECT column1 FROM tablename2)

To check all operator in sql with example execute following script in database to create a tables.

 

create table example1(id int, name varchar(50))

insert into example1 values(1,'suresh'),(2,'dasari'),(3,'rohini'),(4,'madhav'),(5,'honey')

create table example2(id int, name varchar(50))

insert into example2 values(1,'suresh'),(4,'madhav'),(5,'honey')

 

select * from example1

select * from example2

Once we execute above script new tables example1 and example2 will be created in sql database and result will be like as shown below.

 

Create new table in sql server database

 

Now our tables are ready and we can write a queries like as shown following to check ALL Operator.

SQL ALL Operator Example1

Following is the example of using an all operator in sql server.

 

SELECT id,name FROM example1

WHERE id =ALL(SELECT Id FROM example2)

When we execute above sql all operator query, we will get the result like as shown below.

 

SQL Server ALL Operator Example Result or Output

 

If you observe above result we got 0 rows because in above query “id” in example1 table will compare with all ids in example2 table like as shown below.

 

   (example1:id1 = example2:id1)

   AND (example1:id1 = example2:id4)

   AND (example1:id1 = example2:id5)

SQL ALL Operator Example2

Following is the example of using an all operator in sql server. 

 

SELECT id,name FROM example1 WHERE id >=ALL(SELECT Id FROM example2)

When we execute above sql all operator query, we will get the result like as shown below.

 

SQL ALL Operator Example Result or Output

If you observe above result we got a records because in above query “id” column of example1 table will compare with all ids in example2 table like as shown below.

 

   (example1:id1 > example2:id1)

   AND (example1:id1 > example2:id4)

   AND (example1:id1 > example2:id5)

This is how we can use all operator in sql server statements based on our requirements.

 

PREVIOUS

SQL OR Operator