In the database management area, relational operators are the building blocks for data filtering and querying. MySQL, one of the most popular relational database management systems that are widely used across the world, features a wide range of relational operators that are highly efficient in handling and comparing data.
In this article, we will have a look at the role of relational operators in MySQL and ways in which developers can use them effectively and correctly.
What is Relational Operators
Relational operators in MySQL are symbols or keywords to compare the values in SQL queries. They perform comparisons and return a boolean result which is either true or false. Developers can use these operators to check for equality and inequality as well as perform comparisons based on numeric values, strings, or dates.
Different Relational Operators in MySQL
MySQL enables the implementation of the relational operators which are used for the data comparison and serve their purposes separately. Some of the most commonly used relational operators include: Some of the most commonly used relational operators include
- Equal to (=): Equality operator compares if two values are equal. It is usually applied in WHERE clauses to winnow out rows whose value matches a corresponding value.
- Not equal to (!= or <>): Not equal to operator compares two values and returns true if they are not equal. It returns true if values are different. They can be used interchangeably.
- Greater than (>): The > (greater than) operator tests if the left operand is greater than the right operand. It is used to remove the rows where a column value is more than a predefined value.
- Less than (<): The less than sign checks whether the left operand is less than the right operand. It is one of the most popular operators to filter records where the value of a column is smaller than a specified one.
- Greater than or equal to (>=): The “greater than or equal to” operator looks at whether the left operand is greater than or equal to the right operand. It is used to filter the rows when the value of the column is greater than or equal to the given value.
- Less than or equal to (<=): The operator less than or equal to compares the left operand to the right operand to see if it is less than or equal to. It is meant for the elimination of the rows in which a column value is less than or equal to a certain value.
Setup an Environment
--Make the 'employees' table
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary INT
);
-- Fill up the 'employees' table with some sample data.
INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'Alice', 'Smith', 'Sales', 50000),
(2, 'Bob', 'Johnson', 'Marketing', 60000),
(3, 'Charlie', 'Brown', 'IT', 70000),
(4, 'David', 'Lee', 'Finance', 55000);
Output:
+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
| 1 | Alice | Smith | Sales | 50000 |
| 2 | Bob | Johnson | Marketing | 60000 |
| 3 | Charlie | Brown | IT | 70000 |
| 4 | David | Lee | Finance | 55000 |
+-------------+------------+-----------+------------+--------+
Examples of How to use Relational Operators in MySQL
Below are the different operators with examples and output and explanation.
1. Equality Check (=) Operators
Query:
SELECT * FROM employees WHERE department = 'Sales';Output:
+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
| 1 | Alice | Smith | Sales | 50000 |
+-------------+------------+-----------+------------+--------+
Explanation: This query selects all employees from the employees table whose department is ‘Sales‘. It uses the equality operator (=) to compare the values in the department column with the specified value ‘Sales‘.
2. Not Equality Check (!=) Operators
Query:
SELECT * FROM employees WHERE salary != 60000;Output:
+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
| 1 | Alice | Smith | Sales | 50000 |
| 3 | Charlie | Brown | IT | 70000 |
| 4 | David | Lee | Finance | 55000 |
+-------------+------------+-----------+------------+--------+
Explanation: This query retrieves all employees from the employees table whose salary is not equal to 60000. It uses the inequality operator (!=) to filter out rows where the salary is not equal to the specified value
3. Greater Than(>) Operators
Query:
SELECT * FROM employees WHERE salary > 55000;Output:
+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
| 2 | Bob | Johnson | Marketing | 60000 |
| 3 | Charlie | Brown | IT | 70000 |
+-------------+------------+-----------+------------+--------+
Explanation: This query retrieves all employees from the employees table whose salary is greater than 55000. It uses the greater than operator (>) to compare the values in the salary column with the specified value of 55000.
4. Less Than (<) Operators
Query:
SELECT * FROM employees WHERE salary < 60000;Output:
+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
| 1 | Alice | Smith | Sales | 50000 |
| 4 | David | Lee | Finance | 55000 |
+-------------+------------+-----------+------------+--------+
Explanation: This query retrieves all employees from the employees table whose salary is less than 60000. It uses the less than operator (<) to compare the values in the salary column with the specified value of 60000.
5. Greater Than or Equal To (>=) Operators
Query:
SELECT * FROM employees WHERE salary >= 60000;Output:
+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
| 2 | Bob | Johnson | Marketing | 60000 |
| 3 | Charlie | Brown | IT | 70000 |
+-------------+------------+-----------+------------+--------+
Explanation: This query retrieves all employees from the employees table whose salary is greater than or equal to 60000. It uses the greater than or equal to the operator (>=) to compare the values in the salary column with the specified value of 60000.
6. Less Than or Equal To (<=) Operators
Query:
SELECT * FROM employees WHERE salary <= 55000;Output:
+-------------+------------+-----------+------------+--------+
| employee_id | first_name | last_name | department | salary |
+-------------+------------+-----------+------------+--------+
| 1 | Alice | Smith | Sales | 50000 |
| 4 | David | Lee | Finance | 55000 |
+-------------+------------+-----------+------------+--------+
Explanation: This query retrieves all employees from the employees table whose salary is less than or equal to 55000. It uses the less than or equal to the operator (<=) to compare the values in the salary column with the specified value of 55000.
Conclusion
Relational operators are the inseparable features of the SQL developer’s toolkit, which lets the professions filter and query the data mainly with accuracy. Thus, the knowledge of simply how to use relational operators in MySQL is another significant step on the way to discovery in further development of your systems based on the use of the database and an ability to manipulate data and analyze it.