Generally in programming language, operator is used to perform specific mathematical, relational or logical operation and produce results. In SQL, operators are used in an SQL statement’s WHERE clause to perform different operations like comparison, logical and arithmetic operations.
Syntax:
SELECT cols FROM table_name WHERE condition(using operators);
|
For the Logical and Comparison Operators , consider the Employee details table with below data.
Emp_ID | Emp Name | Address | Email id | Salary |
1 | John | CA | 12000 | |
2 | Michel | CA | 13000 | |
3 | Linda | CA | 12500 | |
4 | Michael | CA | 20000 | |
5 | Max | CA | 30000 | |
6 | Smith |
| 5000 |
Logical Operators:Logical operators are used to combine different criteria. The row is selected only when the condition(s) is true.
Operator | Description |
AND | Select row when all the specified conditions are true. |
OR | Select row when any one of the condition is true |
NOT | Select row when all the specified conditions are false. |
BETWEEN | Select row when the values of the between the set of values (between the minimum and maximum value) |
IS NULL | Compare a value with NULL value. |
Example 1:
Get the details of employees having salary anything from 12000 to 13000.
mysql> SELECT * FROM EMP -> WHERE SALARY BETWEEN 12000 AND 13000; +-------------------+--------------------+--------------------+------------------------------+--------+ | emp_id | emp_name | address | emailid | salary | +-------------------+--------------------+--------------------+------------------------------+--------+ | 1 | John | CA | john@abc.com | 12000 | | 2 | Michel | CA | michel@abc.com | 13000 | | 3 | Linda | CA | linda@abc.com | 12500 | +-------------------+--------------------+--------------------+------------------------------+--------+
mysql> SELECT * FROM EMP -> WHERE SALARY = 12000 OR SALARY = 20000; +-------------------+--------------------+--------------------+-------------------------------+--------+ | emp_id | emp_name | address | emailid | salary | +-------------------+--------------------+--------------------+-------------------------------+--------+ | 1 | John | CA | john@abc.com | 12000 | | 4 | Michael | CA | michael@abc.com | 20000 | +-------------------+--------------------+--------------------+-------------------------------+--------+ 2 rows in set (0.05 sec)
Example 3:
Get details of employee who have provided address.
mysql> SELECT * FROM EMP -> WHERE ADDRESS IS NOT NULL; +-------------------+--------------------+--------------------+------------------------------+---------+ | emp_id | emp_name | address | emailid | salary | +-------------------+--------------------+--------------------+------------------------------+---------+ | 1 | John | CA | john@abc.com | 12000 | | 2 | Michel | CA | michel@abc.com | 13000 | | 3 | Linda | CA | linda@abc.com | 12500 | | 4 | Michael | CA | michael@abc.com | 20000 | | 5 | Max | CA | max@abc.com | 30000 | +-------------------+--------------------+--------------------+------------------------------+----------+ 5 rows in set (0.00 sec)
Example 4:
Get details of employees who have not provided address.
mysql> SELECT * FROM EMP -> WHERE ADDRESS IS NULL; +-------------------+--------------------+--------------------+------------------------------+---------+ | emp_id | emp_name | address | emailid | salary | +-------------------+--------------------+--------------------+------------------------------+---------+ | 6 |Smith | |smith@abc.com | 5000 | +-------------------+--------------------+--------------------+------------------------------+----------+ 1 rows in set (0.00 sec)
Comparison Operators
Comparison Operators are similar to Logical Operators, here we use the comparison signs instead of words.
Operator | Description |
> | Greater than |
< | Less than |
>= | Greater than or equal to |
<= | Less than or equal to |
= | Equal to |
<>, != | Not equal to |
Example 1:
Get details of employee who have salary greater than 13000.
mysql> SELECT * FROM EMP -> WHERE SALARY > 13000; +-------------------+--------------------+--------------------+------------------------------+---------+ | emp_id | emp_name | address | emailid | salary | +-------------------+--------------------+--------------------+------------------------------+---------+ | 4 | Michael | CA | michael@abc.com | 20000 | | 5 | Max | CA | max@abc.com | 30000 | +-------------------+--------------------+--------------------+------------------------------+---------+ 2 rows in set (0.00 sec)
Example 2:
Get details of employee who have salary, less than and equal to 15000.
mysql> SELECT * FROM EMP -> WHERE SALARY <=15000; +-------------------+--------------------+--------------------+------------------------------+---------+ | emp_id | emp_name | address | emailid | salary | +-------------------+--------------------+--------------------+------------------------------+---------+ | 1 | John | CA | john@abc.com | 12000 | | 2 | Michel | CA | michel@abc.com | 13000 | | 3 | Linda | CA | linda@abc.com | 12500 | +-------------------+--------------------+--------------------+------------------------------+---------+ 3 rows in set (0.00 sec)
Example 3:
mysql> SELECT * FROM EMP -> WHERE SALARY != 20000; +--------------------+-------------------+--------------------+------------------------------+---------+ | emp_id | emp_name | address | emailid | salary | +--------------------+-------------------+--------------------+------------------------------+---------+ | 1 | John | CA | john@abc.com | 12000 | | 2 | Michel | CA | michel@abc.com | 13000 | | 3 | Linda | CA | linda@abc.com | 12500 | | 5 | Max | CA | max@abc.com | 30000 | +--------------------+-------------------+--------------------+------------------------------+---------+ 4 rows in set (0.01 sec)
Arithmetic Operators:
Arithmetic Operators are used to get the mathematical results. These operators when used would be displayed as columns in the resultset.
Operator | Description |
+ | Addition |
- | Subtraction |
* | Multiplication |
/ | Division |
% | Return the remainder |
Example 1:
mysql> SELECT 10 + 12; +-----------+ | 10 + 12 | +-----------+ | 22 | +-----------+ 1 row in set (0.10 sec)
Example 2:
mysql> SELECT 12 - 10; +----------+ | 12 - 10 | +----------+ | 2 | +----------+ 1 row in set (0.03 sec)
Example 3:
mysql> SELECT 12*10; +---------+ | 12*10 | +---------+ | 120 | +---------+ 1 row in set (0.05 sec)
Example 4:
mysql> SELECT 12/10; +----------+ | 12/10 | +----------+ | 1.2000 | +----------+ 1 row in set (0.07 sec)
Example 5:
mysql> SELECT 12%10; +----------+ | 12%10 | +----------+ | 2 | +-----------+ 1 row in set (0.13 sec)