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 make a comparison of a value with each and 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. ALL operator is headed by a comparison operator and will output true if all of the values of the subquery will fulfill the condition.
- ALL is always used in combination with select, where, having clause of the MySQL.
- ALL is used to select all records or rows of a select query. It compares the value of every value in a list or results from a query. For example, ALL means either greater than every value, means greater than the maximum value, less than every value or equal to ever 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 operations along with comparison values made up 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 values of the subquery. |
| comparison_operator | Compares the expression with the given the subquery. The following are 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 condition
Below 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_operatoris 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 out database.
select column1 from table1 where column1<> ALL(select column1 from table2);
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 one and only 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
Below query will output the list of the average price of the product for each brand product
4.5 (5,030 ratings)
View Course
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 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:
Above output the products whose price is greater than the average price of products 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:
Above output the products whose price is lesser than the average price of products 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 have learned about how to use MySQL ALL operator with the 3 comparison operators like greater than, equal to or less than. All the cases has been described in a much easier way so that the reader can get a complete understanding of the topic after reading this article. All the examples has been explained with MySQL query along with the attached screenshots of the output. We can further extend our understanding of ALL operators with all other comparison operators like <=,>=, or <>, etc.
Recommended Articles
This is a guide to MySQL ALL. Here we discuss the Introduction of MySQL ALL and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
