How to Use Comparison Operator?
We can use Comparison Operator to perform various operations and queries on the tables Let saw some examples for better understanding.
To understand How to use Comparison Operator in MariaDB we need a table on which we will perform various operations and queries. Here we will consider a table called example_table which contains id, first_name and age as Columns.
CREATE TABLE example_table (
id INT PRIMARY KEY,
first_name VARCHAR(50),
age INT
);
INSERT INTO example_table (id, first_name, age) VALUES
(1, 'Aarav', 25),
(2, 'Aisha', 30),
(3, 'Aditya', 22),
(4, 'Esha', 28),
(5, 'Chetan', 35),
(6, 'Lata', 26),
(7, 'Deepak', 32),
(8, 'Sanya', 29),
(9, 'Mayank', 31),
(10, 'Pooja', 24);
Our table example_table looks like:
1. Using Equal to Operator
Syntax:
SELECT * FROM table_name WHERE column_name = value;
Here replace table_name, column_name, and value with your actual table name, column name, and desired value for the condition.
Example:
SELECT * FROM example_table WHERE age = 30;
Output:
Explanation: The above query Retrieve rows where the age is exactly 30.
2. Using Not equal to (!= or <>) Operator
Syntax :
SELECT * FROM table_name WHERE column_name <> value;
Here replace table_name, column_name, and value with your actual table name, column name, and desired value for the condition.
Example :
SELECT * FROM example_table WHERE age <> 28;
Output:
Explanation: The above query Exclude rows where the age is 28.
3. Using Greater than (>) Operator
Syntax:
SELECT * FROM table_name WHERE column_name > value;
Here replace table_name, column_name, and value with your actual table name, column name, and desired value for the condition.
Example:
SELECT * FROM example_table WHERE age > 30;
Output:
Explanation: The above query Retrieve rows where the age is greater than 30.
4. Using Less than (<) Operator
Syntax:
SELECT * FROM table_name WHERE column_name < value;
Here replace table_name, column_name, and value with your actual table name, column name, and desired value for the condition.
Example:
SELECT * FROM example_table WHERE age < 25;
Output:
Explanation : The above query Retrieve rows where the age is less than 25.
5. Using Greater than or equal to (>=) Operator:
Syntax:
SELECT * FROM table_name WHERE column_name >= value;
Here replace table_name, column_name, and value with your actual table name, column name, and desired value for the condition.
Example:
SELECT * FROM example_table WHERE age >= 28;
Output:
Explanation: The above query Retrieve rows where the age is 28 or greater.
6. Less than or equal to (<=) :
Syntax:
SELECT * FROM table_name WHERE column_name <= value;
Here replace table_name, column_name, and value with your actual table name, column name, and desired value for the condition.
Example:
SELECT * FROM example_table WHERE age <= 25;
Output:
Explanation: The above query Retrieve rows where the age is 25 or less.
7. Using LIKE Operator
Syntax:
SELECT * FROM table_name WHERE column_name LIKE 'pattern%';
Here replace table_name, column_name, and pattern with your actual table name, column name, and desired pattern for the condition.
Example:
SELECT * FROM example_table WHERE first_name LIKE 'E%';
Output:
Explanation: The above query Retrieve rows where the name starts with ‘E’.
8. Using IN Operator
Syntax:
SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);
Here replace table_name, column_name, and values with your actual table name, column name, and desired values for the IN condition.
Example:
SELECT * FROM example_table WHERE age IN (26, 29, 32);
Output:
Explanation: The above query Retrieve rows where the age is in the specified list (26, 29, 32).
Comparison Operator in MariaDB
In the world of database management, precise comparisons are essential for accurate data retrieval and manipulation. MariaDB, a powerful open–source relational database system, offers a range of comparison operators to help us filter and query our data effectively. In this article, We will learn about what are Comparison operators and How to use them to get efficient output with the help of various examples and so on.