Updated May 24, 2023
Introduction to MySQL ALL
ALL operators in the MySQL query are used to extract all tuples or records of the select statement. ALL keyword is also used to compare a value with every data in another set of output from a subquery.
- The ALL operator outputs true if and only if the complete subqueries will satisfy the condition. A comparison operator heads ALL operators and will output true if all subquery values fulfill the condition.
- The keyword “ALL” is used in combination with the “SELECT,” “WHERE,” and “HAVING” clauses of MySQL.
- ALL is used to select all records or rows of a select query. It compares every value in a list or results from a query. For example, ALL means greater than every value, greater than the maximum value, less than every value, or equal to every value. Suppose ALL >(1, 2, 3) means greater than 3, ALL < (1,2,3) means less than 1.
Syntax:
Select [col_name1,col_name2….col_name n | exp1]
from table_name
where exp2 comparison operator ALL(subquery);Parameters of MySQL ALL
Below are the parameters and their description of MySQL ALL:
| Parameter | Description |
| col_name1 | Name of the column of the table. |
| Exp1 | The expression can be any arithmetic operation and comparison values of a constant, variable, scalar function, or column name. |
| Table_name | Name of the table. |
| Exp2 | Compares a scalar expression, such as a column, against every value in the subquery for ALL operators. Also, the expression must return a boolean value True for all the subquery values. |
| comparison_operator | Compares the expression with the given subquery. The following is the comparison operator that can be used with the ALL operator(=, <>, !=, >, >=, <, or <=). |
Syntax of ALL operators
select All column_name1, column_name2 from table name where conditionBelow is the syntax of ALL operators with having or where clause:
select column_name1,column_name2 from table_name comparison operator ALL(select column_name from table_name where condition);
expression comparison operator ALL (subquery);Where,
- The expression is any valid expression.
- The comparison_operator is any comparison operator like equal (=), not equal (<>), greater than (>), greater than or equal (>=), less than (<), less than or equal (<=).
- The subquery within the () is a select statement that outputs a result of a single column.
The ALL operator outputs a Boolean value true if all the pairs (expression, c) evaluate to true where c is a value in the single-column output.
If anyone of the pairs (expression, c) returns false, then the ALL operator will return false.
To explain the concept of ALL operators in the MySQL query, we will use a table named product from our database.
select column1 from table1 where column1<> ALL(select column1 from table2);The above example can also be written as:
select column1 from table1 where column1 NOT IN (select column1 from table2);We can use ALL and NOT In the operator in the table of MySQL if the below two conditions are satisfied. They are:
- The table in the subquery statement of the MySQL query must contain only a column or attribute in the table.
- The subquery expression will never depend upon the expression of the column or attribute.
This can also be written as:
Select column1 from table1 where column1 <> ALL (Table table2);Select column1 from table1 where column1 <> (Table table2);Examples to Implement MySQL ALL
The below query will output the list of the average price of the product for each brand product
Example #1
Query:
select avg(price) avg_price from product group by id order by avg_price;Output:
Below is the output of the above query in the output console:
Expression > ALL ( subquery )
The above expression returns a boolean value true if the expression is greater than the maximum value returned by the subquery.
Example #2
Query:
select p_name,price from product where price> ALL(select avg(price) avg_price from product group by id) order by price;Output:
The above output is the products whose price is greater than the average for all brands.
scalar_expression < ALL ( subquery )
The above expression returns to TRUE if the expression is smaller than the lowest value returned by the subquery.
The following example finds the products whose list price is less than the smallest price in the average price list by brand:
Example #3
Query:
select p_name,price from product where price< ALL(select avg(price) avg_price from product group by id) order by price;Output:
The above output is the products whose price is lower than average for all brands.
Likewise, we can apply ALL operators with many other comparison operators like equal to (=), greater than or equal (>=), less than or equal to (<=), and not equal (!=).
Example #4
Next, we have taken table customers to explain ALL operations.
Query:
select * from customer;Output:
Query:
select * from tickets;Output:
Query:
select Cust_Id from customer where Cust_Id <> ALL (select Cust_Id from tickets);Output:
Conclusion
In this tutorial, we learned how to use MySQL ALL operator with the three comparison operators like greater than, equal to, or less than. The article presents all the cases in a simplified manner to ensure that the reader understands the topic completely. We explain each example using a MySQL query and include screenshots to showcase the output. We can further extend our understanding of ALL operators with all other comparison operators like <=,>=, or <>, etc.
Recommended Articles
We hope that this EDUCBA information on “Perl print hash” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
