ALL Operator in SQL Server with Example

  Total Views : 445
  Comments : 0
 
  Keywords : SQL all operator example, How to use all operator in sql server, Syntax of all operator in sql server with example, Different examples of using all operator in sql server

Here we will learn what is all operator in sql server, syntax of all operator in sql server and how to use all operator in sql server queries or procedures.

SQL ALL Operator

The ALL operator in SQL returns true when value matches with all values in single column set of values. It’s like AND operator it will compare value against with all values in column.

Syntax of SQL ALL Operator

Following sql statement is the syntax of ALL operator in sql server

 

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

To check sql all operator with example run following script to create 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 run above script our table’s example1 and example2 will create in sql database and result will be like as shown below 

 

Create new table in sql server database

 

Now our tables are ready write queries like as shown below in examples to check ALL Operator

SQL ALL Operator Example

Now run following sql query to check how to use sql all operator

 

SELECT id,name FROM example1

WHERE id =ALL(SELECT Id FROM example2)

When we run above sql query we will get sql all operator result like as shown below

 

SQL Server ALL Operator Example Result or Output

 

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 Example 2

Write sql query like as shown below to use sql all operator 

 

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

When we run above query it will return sql all operator result like as shown below

 

SQL ALL Operator Example Result or Output

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)

This is how we can use sql all operator in our sql queries or stored procedures.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User