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:

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).

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.