Sql ALL Operator

Sql ALL Operator


The SQL ALL operator are used on subqueries that return multiple values.

The SQL ALL operator returns true if all of the subquery values (single value or from list of values) meet the condition on outer table query.

It can be used on any valid sql select statement with where and having clause.



Sql ALL operator used to t-sql all operator, sql union all operator, and ms sql all operator..

Sql ALL Syntax

The below syntax is used to create all operator with sub query or inner query.


SELECT column-name1, column-name2, ... column-nameN 
FROM table-name 
WHERE column-name operator ALL 
(SELECT column-name FROM table-name WHERE condition)
Note:-
  • The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Sample Database Table - EmpInfo

ID EmpName Gender Age City
1 Harish Karthik Male 31 Hyderbhad
2 Nirmala Female 21 Delhi
3 Keshavan Male 21 Pune
4 Ranjani Mai Female 33 Mysore
5 Geetha Female 30 Chennai
6 Chandra Female 29 Madurai

SQL ALL Operator Example With Numeric Field

The following SQL statement will display records from "EmpInfo" table and It compares a value from main table to each value in a list or results from a subquery and evaluates to true if all of the subquery values meet the condition.


SELECT * FROM EmpInfo 
WHERE Age > ALL (SELECT Age FROM EmpInfo WHERE ID > 4)

The sub-query returns 5 and 6. i.e, These are the ages (30, 29) for which employee "ID" is greater than "4".

Then main query is compared with this, if any of the ages in table is greater than all of the values of sub-query (i.e the ages in table should be greater than both 30 and 29) .

The result of above query is:

ID EmpName Gender Age City
1 Harish Karthik Male 31 Hyderbhad
4 Ranjani Mai Female 33 Mysore

SQL ALL Operator Example With Character Field

The sql 'all' operator will work on a single value (string) and it will not work if the subquery contains multiple values (only unique values). See below statement,


// ERROR STATEMENT
SELECT * FROM EmpInfo 
WHERE Gender = ALL (SELECT Gender FROM EmpInfo)

The sub query will return multiple string values which are "Male" and "Female". So the above query will not return any result and it will display a error message.


SELECT * FROM EmpInfo 
WHERE ID > 4 AND Gender = ALL (SELECT Gender FROM EmpInfo WHERE City = 'Chennai')

The sub-query returns "Female". for which employee "City" is equal to "Chennai".

Then main query is compared with this, if any of the gender in table is equal to all of the values of sub-query.

The result of above query is:

ID EmpName Gender Age City
5 Geetha Female 30 Chennai
6 Chandra Female 29 Madurai


SQL ALL operator using all operator with multiple tables, sql all performance, all operator in subquery, all operator in where clause and select statement, sql all vs in, and sql any vs all operators..

SQL All Operator in Select

The ALL operator is used to select all SELECT STATEMENT tuples. It can also be used to evaluate a value against every other value in a value set or a subquery output.

If all of the subqueries' values satisfy the criterion, the ALL operator returns TRUE. The comparison operators must come before the ALL, and it evaluates to TRUE if the query returns no rows. With the SELECT, WHERE, and HAVING statements, ALL is used.

To select all entries in a SELECT STATEMENT, use ALL. It analyses each value in a list or the results of a query.

Syntax:

SELECT [col_name... | expr1 ]
FROM [tbl_name]
WHERE expr2 comparison_operator {ALL | ANY | SOME} ( subquery )

Example 1: You can use the ALL operator with the less than operator to identify all employees whose wages are less than the lowest salary in the Testing department, as shown below:

SELECT first_name, 
last_name, salary
FROM
    employee
WHERE
    salary < ALL (SELECT salary FROM employees
        WHERE dept_id = 2)
ORDER BY salary DESC;

Example 2:

SELECT ALL ProductName 
FROM Products
WHERE TRUE;

SQL All Operator in Subquery

Only with the ANY and ALL operators in SQL Subqueries can you compare subqueries that return multiple rows. ANY and ALL check whether any or all of the values returned by a subquery meet the expression on the left. It works in the same way as the SOME and IN operators and should be followed by a comparison operator.

Comparisons can be performed on subqueries that generate multiple rows. Once all values received by the subquery satisfy the left-hand expression, it returns true; alternatively, it returns false or the subquery gives no rows. Those subqueries that return one row use comparison operators (=, >, etc.).

"Return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns," says the ANY keyword, which should come after a comparison operator."

Syntax:

scalar_expression comparison_operator ALL < Table subquery>

scalar_expression may be any expression that evaluates to a single value

comparison_operator may be any one of: =, >, <, >=, <=, <> or !=

Example 1: The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.”

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression is TRUE if table t2 contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is unknown (that is, NULL) if table t2 contains (NULL,NULL,NULL).

Example 2: subquery returns no results, the evaluation is still true:

SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2 WHERE num2 > 300);

Output:

num
100
30

SQL All Operator in Where

In SQL, the ALL operator is handy for returning true when the obtained value satisfies all of the values in a single column collection of data. where ALL is used To choose all records in a SELECT STATEMENT, use ALL.

It compares each value in a list or the results of a query. The comparison operators must come before the ALL, and it evaluates to TRUE if the query returns no rows.

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )

WHERE expression2 The ALL operator evaluates each value in the subquery to a scalar expression, such as a column. Every row must meet the criterion in order for the ALL operator to return a Boolean TRUE value.

ALL, for example, denotes a value that is greater than all others, as well as the maximum value. Assume that EVERY (1, 2, 3) is bigger than 3.

WHERE or HAVING uses the ANY and ALL operators.

ANY and ALL are used with subqueries that give numerous results.

Example 1: using an all operator in sql server.

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

Example 2: SQL statement can be used:

SELECT des_date,des_amount,ord_amount
FROM despatch
WHERE des_amount>ALL(
SELECT ord_amount FROM orders
WHERE ord_amount=2000);

SQL All Operator with Condition

When comparing a value to a list or subquery, the ALL comparison condition is applied. It should be accompanied by a list or subquery and preceded by =,! =, >, <, <=, >= With a scalar expression, the ALL operator is available.

Example 1: For example, the following is used with IF condition:

IF 15000 <=  ALL(SELECT salary FROM Employee)
	print('All employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

The expression IF 15000 < all(SELECT salary FROM Employee) is converted as follows using the AND operator:

IF 15000 <= 33000 AND 15000 <= 17000 AND 15000 <= 15000 AND 15000 <= 18000 AND 15000 <= 25000
	print('All employee''s salaries are equal to or more than 15000');
else
	print('Some employee''s salaries are less than 15000');

As a result, the term printed above, All employees are paid at least $15,000 per year.

Example 2: The optimization stretches the initial state to all elements of the list and strings them along with AND operators when the ALL condition is preceded by a list, as illustrated below.

SELECT empno, sal
FROM   emp
WHERE  sal > ALL (2000, 3000, 4000);

Output:

EMPNO SAL
7839 5000

Example 3:

IF 3 > ALL (SELECT ID FROM #TEMP_TABLE)
     PRINT ‘Returned True’
ELSE
     PRINT ‘Returned False’

In the example shown, 3 is greater than 1, 2, and 3. So we can see that 3, when matched to all of 1, 2, and 3, is greater than 1 and 2 but not greater than 3. As a result, 3 is not greater than 1, 2, or 3. As a result, the outcome is false.