05 - SQL Operator

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

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

6

Smith

 

Smith@abc.com

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 |
+-------------------+--------------------+--------------------+------------------------------+--------+

 

Get details of employees having salary 12000 or 20000.
 
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:

Get details of employees whose salary is not equal to 20000
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)

 

Like us on Facebook