Operators in SQL

Operators are generally used in WHERE clause to filter the results. SQL has four categories of operators.

a.   Arithmetic

b.   Comparison

c.   Logical

d.   Reverse

                                 

Arithmetic Operators

Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Modular Division ( a%b gives you reamainder) remainder

mysql> SELECT 10 + 3;
+--------+
| 10 + 3 |
+--------+ 
|     13 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 10 - 3;
+--------+
| 10 - 3 |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 10 * 3;
+--------+
| 10 * 3 |
+--------+
|     30 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 10 / 3;
+--------+
| 10 / 3 |
+--------+
| 3.3333 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT 10 % 3;
+--------+
| 10 % 3 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Let us create an employee table.

CREATE TABLE employee(

        id int PRIMARY KEY,

        name VARCHAR(20),

        salary FLOAT(10, 3)

);           

INSERT INTO employee VALUES (1, "Hari Krishna", 12345.678);

INSERT INTO employee VALUES (2, "Kiran", 45000);

INSERT INTO employee VALUES (3, "Senthil", 75492.98);

INSERT INTO employee VALUES (4, "Prasob", 48000);

mysql> CREATE TABLE employee(
    -> id int PRIMARY KEY,
    -> name VARCHAR(20),
    -> salary FLOAT(10, 3)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employee VALUES (1, "Hari Krishna", 12345.678);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (2, "Kiran", 45000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (3, "Senthil", 75492.98);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (4, "Prasob", 48000);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+----+--------------+-----------+
| id | name         | salary    |
+----+--------------+-----------+
|  1 | Hari Krishna | 12345.678 |
|  2 | Kiran        | 45000.000 |
|  3 | Senthil      | 75492.977 |
|  4 | Prasob       | 48000.000 |
+----+--------------+-----------+
4 rows in set (0.00 sec)

mysql>
mysql> SELECT name, 1.5*salary FROM employee;
+--------------+------------+
| name         | 1.5*salary |
+--------------+------------+
| Hari Krishna |  18518.517 |
| Kiran        |  67500.000 |
| Senthil      | 113239.465 |
| Prasob       |  72000.000 |
+--------------+------------+
4 rows in set (0.00 sec)

SELECT name, 1.5*salary FROM employee;

As you see above statement, it multiplies the salary of employee with 1.5 before returning.

Comparison Operators

Operator	Description
=	a=b : Condition true if a is equal to b, else false
<>	a <> b : Condition true if a is not equal to b, else false
<	a < b : Condition true if a is less than b, else false.
<=	a <= b : Condition true if a is less than or equal to b, else false.
>	a > b : Condition true if a is greater than b, else false.
>=	a >= b : Condition true if a is greater than or equal to b, else fals.e
!<	a !< b : Condition true if a is not less than b, else false.
!>	a !> b : Condition true is a is not greater than b, else false.

mysql> SELECT * FROM employee;
+----+--------------+------------+
| id | name         | salary     |
+----+--------------+------------+
|  1 | Hari Krishna |  12345.678 |
|  2 | Kiran        |  45000.000 |
|  3 | Senthil      |  75492.977 |
|  4 | Prasob       |  48000.000 |
|  5 | VadiRaj      |  95492.977 |
|  6 | Srinath      | 295492.969 |
+----+--------------+------------+
6 rows in set (0.00 sec)

mysql> DESCRIBE employee;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| salary | float(10,3) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

1. Get all employees whose name is ‘Kiran’.

mysql> SELECT * FROM employee WHERE name = "Kiran";
+----+-------+-----------+
| id | name  | salary    |
+----+-------+-----------+
|  2 | Kiran | 45000.000 |
+----+-------+-----------+
1 row in set (0.00 sec)

2. Select all employees whose salary > 50000.

mysql> SELECT * FROM employee WHERE salary > 50000;
+----+---------+------------+
| id | name    | salary     |
+----+---------+------------+
|  3 | Senthil |  75492.977 |
|  5 | VadiRaj |  95492.977 |
|  6 | Srinath | 295492.969 |
+----+---------+------------+
3 rows in set (0.00 sec)

3. Select id and name of employees whose ids are < 3.

mysql> SELECT id, name FROM employee WHERE id < 3;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | Hari Krishna |
|  2 | Kiran        | 
+----+--------------+
2 rows in set (0.00 sec)

Logical operators are explained in next post.

Previous                                                 Next                                                 Home