MySQL Operators
MySQL Operators
MySQL operator needs advanced operation on the table and its data. This operator works with the "WHERE" clause. MySQL operators are a statement to modify information. It helps to handle a group of the data and apply expression with the condition. The operator uses logical, numerical, conditional statements. The operator mainly uses it for the following operations.
- Searching data: the operator supports searching data from a MySQL table.
- Sorting data: MySQL operator sorts the data from the table as per requirement.
- Filtering data: MySQL operator provides to avoid unwanted data and display the required table information.
This operator helps to search for particular information from the table. It supports sorting the data from the table as per requirement. It displays required information and avoids unwanted data. It works to know about table information such as exist, null, or not null. You get information with the condition and required pattern.
MySQL operator uses the "WHERE" clause. The below operators use information from the database.
| MySQL Operators | Description |
| MySQL "LIKE" operator | The "LIKE" operator is used to get a particular string's data. |
| MySQL "BETWEEN" operator | The "BETWEEN." operator helps to Match numerical values. |
| MySQL "IN" operator | The "IN" operator searches data available in the table. |
| MySQL "ANY" operator | The "ANY" operator matches any data in given conditions. |
| MySQL "EXISTS" operator | This operator helps to find if data is available or not. |
| MySQL "IS NULL" operator | This operator helps find a null column in the table. |
| MySQL "IS NOT NULL" operator | This operator helps find a not null column in the table. |
The "like" operator always works with patterns to search data. The "between," "IN," and "ANY" operator works to search, sort, and filter the data from a table. The "Exist," "IS NULL," and "IS NOT NULL" operator is used to check the availability of the table data. Here, you get every data information without interruption and difficulties.
Syntax
MySQL operator comes with expression, clause, condition, and pattern. The syntax is below.
SELECT column (*) FROM table-name WHERE condition Operator pattern;
The syntax helps you to know the working procedure of the MySQL operators. You use any MySQL operator as per requirement.
Example
Select * from mysql_tutorial where topic LIKE '%mysql%';
Here, MySQL query uses LIKE operator with a required pattern. You use any operator as per condition and requirement. Here you know the working procedure of the MySQL operator. The operator always comes with a "WHERE" clause.
MySQL "LIKE" operator
The "LIKE" operator is used to find matching data. The "LIKE" operator provides a pattern to apply the condition. The "LIKE" operator matches the required number, character, or string in the database.
This operator works for insert, update, retrieve, and delete operations. The MySQL "LIKE" operator comes with "WHERE" clauses in the query. The MySQL "LIKE" operator mainly supports string and character data.
Syntax
The basic LIKE syntax is below.
MySQL Expression LIKE pattern;
The usable LIKE syntax is below.
SELECT column (*) FROM table-name WHERE condition LIKE pattern;
The operator uses two types of the symbol in the pattern. This symbol helps to search and sort data from the table.
- Percentage (" % ")
- Underscore (" _ ")
Percentage (" % ")
The percentages (" % ")sign matching strings or multiple characters of the database. It helps to search for more than one character in the value. The symbol helps to match the required string in the initial, middle, or end of the value.
Syntax
If you want to find the required string at the initial data, use the following syntax.
SELECT column (*) FROM table-name WHERE condition LIKE '%my'; If you want to find the required string at the end of the data, use the following syntax. SELECT column (*) FROM table-name WHERE condition LIKE 'ql%'; If you want to find the required string in the middle of the data, use the following syntax. SELECT column (*) FROM table-name WHERE condition LIKE '%sq%';
Underscore (" _ ")
The underscore symbol (" _ ")is used to match a single character of the data. If a user wants to match the required string in the string, then use the following syntax. If a user matches the required string, such as the initial letter is "m" and the final letter is "l."
Syntax
SELECT column (*) FROM table-name WHERE condition LIKE 'm_l';
Examples of the Like operator
1) Example: Matches an initial string of the table data.
Execute the below query to match required data using the percentage symbol.
mysql> select topic, time, level from mysql_tutorial where a topic like 'my%';
OUTPUT
The output gets data to match the initial string from the table.
You see the four rows displayed in the above image. The "topic" column contains the "my" keyword at the initial position of the value.
2) Example: Matches last strings of the MySQL data table.
Execute the below query to match required data using the percentage symbol.
mysql> select topic, time, level from mysql_tutorial where a topic like '%ry';
OUTPUT
The output gets data to match the last string from the table.
You see the one-row displays in the above image. The "topic" column contains the "ry" keyword at the end position of the value.
3) Example: Matches required strings of the MySQL data table.
Execute the below query to match required data using the percentage symbol.
mysql> select topic, time, level from mysql_tutorial where a topic like '%er%';
OUTPUT
The output gets data to match the middle string from the table.
You see the two rows displayed in the above image. The "topic" column contains the "er" keyword any middle position of the value.
MySQL "BETWEEN" operator
The MySQL "BETWEEN" operator finds data between initial and final values. The "BETWEEN" operator provides AND keywords to apply the condition. The MySQL "BETWEEN" operator matches the required number and logical condition in the table.
It is a logical operator that works for a search, update, retrieve, and delete operation. The MySQL BETWEEN operator uses "WHERE" clauses in the MySQL expression.
The MySQL "between" operator works for the numerical values. If the operator tries to use a string, then the table does not display values. The output shows an empty set of the table data.
Syntax
The basic syntax of the "between" operator is below.
MySQL Expression BETWEEN initial value AND final value; The syntax of the "not between" operator is below. MySQL Expression NOT BETWEEN initial value AND final value;
MySQL query syntax of the "between" operator is below.
SELECT column (*) FROM table-name WHERE condition BETWEEN initial value AND final value;
Examples of the between the operator
1) Example: the example uses MySQL "between" operator.
The MySQL "between" operator uses the numerical value of the table.
mysql> select topic, time from mysql_tutorial where index_number between 1 and 3;
OUTPUT
The output displays the required data of the database.
Here, you get the three rows between 1 and 3 of the "index_number" column.
2) Example: the example uses MySQL "between" operator.
The MySQL "between" operator uses the numerical value of the table. Execute the below query to get information.
mysql> select topic, time from mysql_tutorial where index_number between 4 and 8;
OUTPUT
The output displays the required data of the database.
Here, you get the one row between 4 and 8 of the "index_number" column. The available data displays as an output using a query.
3) Example: the example uses MySQL "between" operator.
The MySQL "between" operator uses the numerical value of the table. Execute the following query to get conditional information.
mysql> select * from mysql_tutorial where index_number between 5 and 8;
OUTPUT
The output displays the required data of the database.
Here, you do not get any data from the table. If data does not become available, then output displays an empty set.
4) Example: the example uses MySQL "not between" operator.
The MySQL "not between" operator uses the numerical value of the table. Execute the following query to get conditional information.
mysql> select index_number, topic, time from mysql_tutorial where index_number not between 1 and 3;
Output
The output displays the required data of the database.
Here, you get the one row using the "not between" operator from 1 and 3 of the "index_number" column.
MySQL "IN" operator
The "IN" operator is used to find value from given data. The "IN" operator displays the value which is given in the required list. The values are placed inside of the "IN" operator. The values are separated using the comma (,) symbol. This operator is used with the "WHERE" clause.
This operator works like the "OR" condition. The IN operator provides multiple "OR" operations in a single query. This operator reduces the complexity of the "OR" operators and handles data.
The "NOT IN" operator works the opposite function of the "IN" operator. If the given value is not available in the table, then the output shows an empty set. This operator supports numerical and string values.
Syntax
The basic syntax of the "IN" operator is below.
MySQL Expression IN (val1, val2, .., valN); The basic syntax of the "NOT IN" operator is below. MySQL Expression NOT IN (val1, val2, .., valN); MySQL query syntax with "IN" operator is below. SELECT column (*) FROM table name WHERE condition IN (val1, val2, .., valN);
Examples of the IN operator
1) Example: the example uses MySQL "IN" operator for numerical value.
Execute the below query to filter information and get the required data. The "IN" operator uses numerical values.
mysql> select * from mysql_tutorial where index_number IN (1, 3, 5, 6);
OUTPUT
The above output shows the first and third index numbers data. Here "IN" operators handle numerical values.
2) Example: the example uses MySQL "IN" operator for the string value.
Execute below query to filter information and get required data. The "IN" operator uses string values.
mysql> select * from mysql_tutorial
-> where topic
-> IN ('mysql table', 'mysql database', 'mysql setup', 'mysql operator');
OUTPUT
The above output shows the required topic from the table. Here "IN" operators handle string values.
3) Example: the example uses the MySQL "NOT IN" operator for numerical values.
Execute the below query to filter information and get the required data. The "NOT IN" operator uses numerical values.
mysql> select * from mysql_tutorial where index_number NOT IN (1, 3, 4);
OUTPUT
The above output does not show first and third index numbers data. Here "NOT IN" operators handle numerical values.
4) Example: the example uses MySQL "NOT IN" operator for the string value.
Execute below query to filter information and get required data. The "NOT IN" operator uses string values.
mysql> select * from mysql_tutorial
-> where topic
-> NOT IN ('mysql table', 'mysql database', 'mysql setup', 'mysql operator');
OUTPUT
The above output shows two topics from the table. Here "NOT IN" operators handle string values.
MySQL "EXISTS" Operator
The "EXISTS" operator is a Boolean operator to check value. This operator verifies the availability of the data in the table. MySQL "EXISTS" operator returns true or false condition as per the availability. This operator works with the "WHERE" clause as a condition. This clause assigns a column for execution.
The "NOT EXISTS" operator works the opposite function of the "EXISTS" operator. This Boolean operator used a subquery to the presence of the data. The "Exists" operator compares two tables and returns the Boolean output.
Syntax
The basic syntax of the "EXISTS" operator is below.
MySQL Expression EXISTS (subquery);
The basic syntax of the "NOT EXISTS" operator is below.
MySQL Expression NOT EXISTS (subquery); MySQL query syntax with the "EXISTS" operator is below. SELECT column (*) FROM table-name WHERE EXISTS (subquery);
MySQL query syntax with the "EXISTS" operator is below.
SELECT column (*) FROM table-name WHERE [NOT] EXISTS (SELECT column (*) FROM table-name WHERE condition);
Examples of the EXISTS operator
1) Example: the example uses MySQL "EXISTS" operator.
Execute the below query to get output.
mysql> select topic, time, level from mysql_tutorial where EXISTS ( select topic, time from mysql_tutorial where index_number > 3);
OUTPUT
You see the available data of the table and fulfill at least one condition.
2) Example: the example uses MySQL "EXISTS" operator.
If the subquery of the existing operator does not fulfill the "where" condition, then output shows an empty set. Execute the below query to get output.
mysql> select topic, time, level from mysql_tutorial where EXISTS ( select topic, time from mysql_tutorial where index_number > 5);
OUTPUT
The "index_number" column includes four rows. MySQL "where" condition is defined as greater than "5" index. The output displays an empty set.
3) Example: the example uses MySQL "EXISTS" operator.
Execute the below query to get Boolean output.
mysql> select EXISTS ( select topic, time from mysql_tutorial where time = 'half hour');
OUTPUT
You see the above output of the "EXISTS" operator. The Boolean value shows "1" means data is available.
4) Example: the example uses MySQL "NOT EXISTS" operator.
Execute the below query to get Boolean output. The "NOT EXISTS" operator shows the opposite output of the "EXISTS" operator.
mysql> select NOT EXISTS ( select topic, time from mysql_tutorial where time = 'half hour');
OUTPUT
You see the above output of the "NOT EXISTS" operator. The Boolean value shows "0" means data is available.
MySQL "IS NULL" operator.
The "IS NULL" operator verifies or checks null information in the table. This operator helps with the main data table and updates with value. The "IS NULL" operator checks if the table values are null or not. If values are null, then table data displays the output. The MySQL "WHERE" clause is necessary to operate the "IS NULL" operator.
Syntax
The basic syntax of "IS NULL" shows below.
MySQL Expression IS NULL; MySQL query syntax of "IS NULL" shows below. SELECT column (*) FROM table-name WHERE condition IS NULL;
Examples of the IN operator
1) Example: the example uses MySQL "IS NULL" operator.
Execute the below query to get the entire value of the table. If a null value is available, then the query shows a null value.
mysql> select topic, time, level from mysql_tutorial where level IS NULL;
OUTPUT
The above output displays the "null" value of the table. Here, the level column shows the available null value from the table.
2) Example: the example uses MySQL "IS NULL" operator.
Execute the below query to get the entire value of the table. If a null value is available, then the query shows an empty set.
mysql> select topic, time, level from mysql_tutorial where topic IS NULL;
OUTPUT
Here, you see the output of the given query. The "topic" column does not contain a null value. Therefore, the output displays an empty set.
MySQL "IS NOT NULL" operator.
If you want to check about table columns and their values, use the "IS NOT NULL" operator. This operator checks if the table values are null or not. MySQL "IS NOT NULL" operator displays available information from the table. This operator does not show null values of the table.
Syntax
The basic syntax of the "IS NOT NULL" operator shows below.
MySQL Expression IS NOT NULL; MySQL query syntax of the "IS NOT NULL" operator shows below. SELECT column (*) FROM table-name WHERE condition IS NOT NULL;
Examples of the "IS NOT NULL" operator
1) Example: the example uses MySQL "IS NOT NULL" operator.
Execute the below query to get not null data from the table.
mysql> select topic, time from mysql_tutorial where topic IS NOT NULL;
OUTPUT
Available information displays from the given table. You see the output of the "IS NOT NULL" operator. The "topic" column does not display a null value. Four rows contain data in the MySQL table.
2) Example: the example uses MySQL "IS NOT NULL" operator.
Execute the below query to get not null data from the table.
mysql> select topic, time, level from mysql_tutorial where level IS NOT NULL;
OUTPUT
You see the output of the "IS NOT NULL" operator. The "level" column does not display a null value. Three rows contain data in the MySQL table.
MySQL "ANY" operator
The "ANY" operator supports the Boolean condition. Sometimes, you require any information and part of the data in the table. The "Any" operator gives at least minimum similarities to the MySQL table. This operator gives at least one similarity of the data. The "ANY" operator is providing comparison conditions.
This operator is using "equal to," "less than," "greater than," "not equal to," "less than equal to," and "greater than equal to" conditions. The MySQL "ANY" operator mainly supports numerical values to apply regular expressions.
The "ANY" operator compares two values from two tables. You correlate and compare table data using this operator.
Syntax
The basic syntax of the ANY operator is below.
MySQL Expression comparison conditions ANY (SUB QUERIES) ; MySQL query using ANY operator syntax is below. SELECT column (*) FROM table-name WHERE column comparison conditions ANY (SUBQUERIES) ;
How do works "ANY" operators?
1) Step: create the first table.
Execute the below query to create the first table.
mysql> create table first_table( marks int, percentage float);
2) Step: insert the value in the table.
Insert information in the first table.
mysql> insert into first_table( marks, percentage) values (356 , 73.0), (300, 60), (411, 82.20);
The table and its data displays below.
Select * from first_table;
3) Step: create a second table.
Execute below query to create a second table.
mysql> create table sec_table( marks int, percentage float);
4) Step: insert the value in the table
Insert information in the second table.
mysql> insert into sec_table( marks, percentage) values (350 , 70.0), (300, 60), (445, 89.0);
The table and its data displays below.
Select * from sec_table;
If you want to compare two tables then, use the "ANY" operator with comparison expressions.
Examples of the ANY operator
1) Example: the example uses MySQL "ANY" operator.
Execute the below query with the "greater than" expression for the first table.
mysql> select marks from first_table where marks > ANY (select marks from sec_table);
OUTPUT
You see the above output with the marks column. The output shows only the first table values after comparing them with the second table.
2) Example: the example uses MySQL "ANY" operator.
Execute the below query with the "equal to" expression for the first table.
mysql> select marks from first_table where marks = ANY (select marks from sec_table);
OUTPUT
You see the above output with the marks column. The output shows only the first table values after comparing them with the second table. The equal values show in the output image.
3) Example: the example uses MySQL "ANY" operator.
Execute below query with "greater than" expression for second table.
mysql> select * from sec_table where marks > ANY (select marks from first_table);
OUTPUT
You see the above output with marks and percentage column. The output shows only second table values after comparing with the first table.