MySQL Operators

MySQL Operators

In this tutorial, we are going to discuss about the MySQL Operators. In MySQL, operators are symbols used to perform operations on one or more operands. Following are the types of operators in SQL.

  1. Arithmetic Operators
  2. Logical Operators
  3. Compound Operators
  4. Comparison Operators
  5. String Operators
  6. Null Operators
  7. Date and Time Operators
  8. Set Operators
  9. Miscellaneous Operators
Arithmetic Operators

Arithmetic Operators perform mathematical calculations such as Addition, Subtraction, Multiplication, Module, and Division. The arithmetic operators can be directly used in the SQL statement or combination with column values. Here are some common arithmetic operators in MySQL:

MySQL Operators

Example

SELECT 10 + 5 AS Sum, 10 - 5 AS Difference, 10 * 5 AS Product, 10 / 5 AS Quotient, 10 % 3 AS Remainder;
Logical Operators

In MySQL, logical operators combine or compare conditions in a WHERE clause of a SQL query. These operators allow you to create more complex conditions by combining multiple conditions. Here are some logical operators in MySQL:

Logical Operators

Example

SELECT * FROM employees WHERE salary > 50000 AND department = 'Engineering';
Compound Operators

In MySQL, compound operators are shorthand notations that combine an arithmetic or bitwise operation with an assignment. These operators operate and assign the result to a variable in a single step. Here are some common compound operators in MySQL:

Compound Operators
Comparison Operators

Comparison operators in MySQL are used to compare values in various conditions within SQL queries. They allow you to create conditions determining which rows should be included in the result set. Here are some common comparison operators in MySQL:

Comparison Operators 1

Example

SELECT * FROM employees WHERE salary > 50000;
String Operators

String Operators are used for string manipulation.

  • CONCAT() : Concatenates two or more strings.
  • LIKE : Searches for a pattern.
  • IN : Checks if a value matches any value in a list.
  • NOT LIKE : Searches for a pattern but does not match.
SELECT CONCAT(first_name, ' ', last_name) AS FullName FROM employees WHERE last_name LIKE 'S%';
Null Operators

Used to test for NULL values.

  • IS NULL : Checks if a value is NULL.
  • IS NOT NULL : Checks if a value is not NULL.

Example:

SELECT * FROM employees WHERE middle_name IS NULL;
Date and Time Operators

Used for date and time calculations.

  • NOW() : Returns the current date and time.
  • CURDATE() : Returns the current date.
  • DATE_ADD() : Adds a time interval to a date.
  • DATEDIFF() : Returns the difference between two dates.

Example:

SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 DAY), DATEDIFF('2024-12-31', '2024-01-01') AS DaysDifference;
Set Operators

Used to combine the results of two SELECT queries.

  • UNION : Combines the results of two queries (removes duplicates).
  • UNION ALL : Combines the results of two queries (includes duplicates).
  • INTERSECT : Returns the common records.
  • EXCEPT or MINUS : Returns records from the first query that are not in the second.

Example:

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;
Miscellaneous Operators
  • BETWEEN : Checks if a value is within a range.
  • LIKE : Searches for a specified pattern.
  • IS NULL / IS NOT NULL : Checks for NULL values.

Example:

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

These MySQL operators are fundamental for performing various operations on data within MySQL, enabling complex queries and data manipulations.

That’s all about the MySQL operators. If you have any queries or feedback, please write us at contact@waytoeasylearn.com. Enjoy learning, Enjoy SQL..!!

MySQL Operators
Scroll to top