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.

What are Comparison Operators?

Comparison operators in MariaDB are used to compare values in expressions and return a boolean result (TRUE or FALSE) based on the comparison. These operators allow us to perform various types of comparisons, such as equal to, not equal to, greater than, less than, etc.

Comparison operators are fundamental in SQL queries as they enable you to filter data based on specific conditions, making your queries more selective and powerful. Below are the most widely used Comparison operators in MariaDB to perform various operations and queries.

The following are the comparison operators used in MariaDB:

Operators	Description
`=`	Selects rows where the specified column is equal to a given value.
`!=` or `<>`	Excludes rows where the specified column is equal to a given value.
`>`	Retrieves rows where the specified column is greater than a given value.
`<`	Retrieves rows where the specified column is less than a given value.
`>=`	Retrieves rows where the specified column is greater than or equal to a given value.
`<=`	Retrieves rows where the specified column is less than or equal to a given value.
`IS NULL`	Retrieves rows where the specified column contains NULL values.
`IS NOT NULL`	Retrieves rows where the specified column does not contain NULL values.
`LIKE`	Retrieves rows where the specified column matches a specific pattern.
`NOT LIKE`	Retrieves rows where the specified column does not match a pattern.
`IN`	Retrieves rows where the specified column has values present in a given list.
`NOT IN`	Retrieves rows where the specified column does not have values present in a given list.

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:

Showing Contents Of example_table

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:

Output of Equal to operator

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:

Output of Not equal to operator

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:

Output of Greater than operator

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:

Output of Less than operator

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:

Output of Greater than or equal to operator

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:

Output of Less than or equal to operator

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:

Output of Like operator

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:

Output for IN operator

Explanation: The above query Retrieve rows where the age is in the specified list (26, 29, 32).

Conclusion

Overall, Writing accurate and productive SQL queries in MariaDB requires a thorough understanding of and proficiency with comparison operators. These operators enable you to filter and obtain data according to certain criteria. These users include database administrators, developers, and beginner in data administration. Now after reading this article you have much knowledge about Comparison Operator and you can perform as many examples.