EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL ALL
 

MySQL ALL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 24, 2023

MySQL ALL

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

  • 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 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_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.

Note: Not IN can also be written as <> ALL. Below is the syntax of the not-in operator in MySQL.
Example:
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:

Mysql ALL Example 1

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:

Mysql ALL Example 2

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:

Mysql ALL Example 3

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:

Customer Details Example 4A

Query:

select * from tickets;

Output:

Customer Details Example 4B

Query:

select Cust_Id from customer where Cust_Id <> ALL (select Cust_Id from tickets);

Output:

Customer Details Example 4C

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.

  1. Introduction to MySQL Operators
  2. Top 23 MySQL String functions
  3. MySQL vs SQLite | Top 14 Comparisons
  4. Guide to MySQL Timestamp

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW