MySQL - SPLessons

MySQL Operators

Chapter 7

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL Operators

MySQL Operators

shape Description

To retrieve data from the table based on some rules, then use conditional operators. Conditional operators returns true or false esteem based on the instance of the variables.

The conditional operators in MySQL are classified into following types:

shape Conceptual
figure

Logical AND Operator

shape Description

To specify multiple conditions in the column of MySQL table use Logical AND operator. i.e. MySQL Logical And operator compares two results and returns true if both of the results are true.

shape Syntax

Select  * from <table_name> where <condition1> and <condition2> and <condition3>;

table_name => Any accurate table.

condition => The condition is a logic to get a specific records.

shape Examples

By viewing the below example, the concept of Logical And operator can be easily understood from the table.

mysql> select * from employee;
+--------+-------+-------+--------+-----------+
| emp_id | ename |salary | deptno | job       |
+--------+-------+-------+--------+-----------+
|   1001 | mike  | 12000 |     10 | manager   |
|   1002 | rambo | 13000 |     20 | scalesman |
|   1003 | kate  | 14000 |     10 | manager   |
|   1003 | jeo   | 14000 |     20 | manager   |
|   1003 | finn  | 14000 |     30 | manager   |
+--------+-------+-------+--------+-----------+
5 rows in set (0.00 sec)

mysql>select * from employee where ename='mike'and job='manager';
+--------+-------+-------+--------+---------+
| emp_id | ename |salary | deptno | job     |
+--------+-------+-------+--------+---------+
|   1001 | mike  | 12000 |     10 | manager |
+--------+-------+-------+--------+---------+
1 row in set (0.00 sec)

mysql>select * from employee where ename='finn' and job='manager';
+--------+-------+-------+--------+---------+
| emp_id | ename |salary | deptno | job     |
+--------+-------+-------+--------+---------+
|   1003 | finn  | 14000 |     30 | manager |
+--------+-------+-------+--------+---------+
1 row in set (0.00 sec)

The above example tells that, when Logical and operation is performed on a column like ename=’mike’and job=’manager’, then it automatically displays the entire details of that employee like emp_id,salary and deptno.

Logical OR Operator

shape Description

To specify multiple conditions on different columns in MySQL table use Logical OR operator. MySQL Logical OR operator compares two expressions and returns true if both of the expressions is valid.

shape Syntax

Select * from <table_name> where <condition1> or <condition2> or <condition3>;

table_name => Any accurate table.

condition => The condition is a logic to get specific records.

shape Examples

By viewing the below example, the concept of Logical OR operator can be easily understood from the table.

mysql> select * from employee;
+--------+-------+-------+--------+-----------+
| emp_id | ename | salary| deptno |   job     |
+--------+-------+-------+--------+-----------+
|   1001 | mike  | 12000 |     10 | manager   |
|   1002 | rambo | 13000 |     20 | scalesman |
|   1003 | kate  | 14000 |     10 | manager   |
|   1003 | jeo   | 14000 |     20 | manager   |
|   1003 | finn  | 14000 |     30 | manager   |
+--------+-------+-------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from employee where ename='rambo' or sal>13000;
+--------+-------+-------+--------+-----------+
| emp_id | ename |salary | deptno |   job     |
+--------+-------+-------+--------+-----------+
|   1002 | rambo | 13000 |     20 | scalesman |
|   1003 | kate  | 14000 |     10 | manager   |
|   1003 | jeo   | 14000 |     20 | manager   |
|   1003 | finn  | 14000 |     30 | manager   |
+--------+-------+-------+--------+-----------+
4 rows in set (0.00 sec)

mysql> select * from employee where ename='mike' or sal=12000;
+--------+-------+-------+--------+-----------+
| emp_id | ename |salary | deptno |   job     |
+--------+-------+-------+--------+-----------+
|   1001 | mike  | 12000 |     10 | manager   |
+--------+-------+-------+--------+-----------+
4 rows in set (0.00 sec)

The above example tells that, when Logical OR operation is performed on a column like ename=’rambo’ and salary>13000 then it automatically displays the entire details of employee rambo and shows the salaries of employees greater than equal to 13000.

IS NOT NULL Operator

shape Description

The IS NOT NULL operator will review whether the esteem IS Not Null or not.

shape Syntax

select * from <table_name> where <column_name> is not null.

table_name =>Any accurate table.

column_name =>The condition that one can perform on a column by using IS NOT NULL operator.

shape Examples

By viewing the below example, the concept of IS NOT NULL can be easily understand from the table.

mysql> select * from employee;
+--------+--------+----------+-------+------------+
| emp_id | ename  |   job    |salary | commission |
+--------+--------+----------+-------+------------+
|   1001 | kate   | manager  | 14000 |         10 |
|   1002 | jack   | scaleman | 13000 |         20 |
|   1003 | maddie | business | 14000 |         30 |
|   1004 | madd   | business | 14000 |       NULL |
+--------+--------+----------+-------+------------+
4 rows in set (0.00 sec)

mysql> select * from employee where commission is not null;
+--------+--------+----------+-------+------------+
| emp_id | ename  |   job    |salary | commission |
+--------+--------+----------+-------+------------+
|   1001 | kate   | manager  | 14000 |         10 |
|   1002 | jack   | scaleman | 13000 |         20 |
|   1003 | maddie | business | 14000 |         30 |
+--------+--------+----------+-------+------------+
3 rows in set (0.00 sec)

The above example tells that, when IS NOT NULL operation is performed on a column name commission then it automatically remove the null values from column and gives the output.

IS NULL Operator

shape Description

MySQL IS NULL operator will review whether a esteem is NULL.

shape Syntax

Select * from <table_name> where <column_name> is null;

table_name =>Any accurate table.

column_name =>The condition that one can perform on a column by using IS NULL operator.

shape Examples

By viewing the below example, the concept of IS NULL can be easily understood from the table.

mysql> select * from employee;
+--------+--------+----------+-------+------------+
| emp_id | ename  |   job    |salary | commission |
+--------+--------+----------+-------+------------+
|   1001 | kate   | manager  | 14000 |         10 |
|   1002 | jack   | scaleman | 13000 |         20 |
|   1003 | maddie | business | 14000 |         30 |
|   1004 | madd   | business | 14000 |       NULL |
+--------+--------+----------+-------+------------+
4 rows in set (0.00 sec)

mysql> select * from employee where commission is null;
+--------+-------+----------+-------+------------+
| emp_id | ename |   job    |salary | commission |
+--------+-------+----------+-------+------------+
|   1004 | madd  | business | 14000 |       NULL |
+--------+-------+----------+-------+------------+
1 row in set (0.00 sec)

The above example tells that, when IS NULL operation is performed on a column name commission then it shows the null values column details like emp_id, ename, job and salary.

BETWEEN AND OPERATOR

shape Description

MySQL BETWEEN AND operator checks whether a value is present between minimum and maximum range of an expression.

shape Syntax

Select * from <table_name> where <column_name> between minimum and maximum;

table_name =>Any accurate table.

column_name =>The condition that one can perform on a column by using Between and operator.

shape Examples

By viewing the below example, the concept of Between And operations can be easily understood from the table.

mysql> select * from employee;
+--------+--------+-----------+-------+------------+
| emp_id | ename  |   job     |salary | commission |
+--------+--------+-----------+-------+------------+
|   1001 | kate   | manager   | 14000 |         10 |
|   1002 | jack   | scaleman  | 13000 |         20 |
|   1003 | maddie | business  | 14000 |         30 |
|   1004 | madd   | business  | 14000 |       NULL |
|   1005 | winni  | marketing | 14500 |       NULL |
+--------+--------+-----------+-------+------------+
5 rows in set (0.00 sec)

mysql> select * from employee where sal> 13000 and sal<14500;
+--------+--------+----------+-------+------------+
| emp_id | ename  |   job    |salary | commission |
+--------+--------+----------+-------+------------+
|   1001 | kate   | manager  | 14000 |         10 |
|   1003 | maddie | business | 14000 |         30 |
|   1004 | madd   | business | 14000 |       NULL |
+--------+--------+----------+-------+------------+
3 rows in set (0.00 sec)

The above example tells that, when Between and operation is performed on a column name salary, then it automatically show the salary, according to the condition and  gives the output.

NOT EQUAL Operator

shape Description

MySQL NOT EQUAL operator is used to return a set of rows from the table, after making sure that two expressions placed on either sides of the table are NOT NULL.

shape Syntax

Select * from <table_name> where <column_name> is not equal to;

table_name =>Any accurate table.

column_name =>The condition that one can perform on columns by using Not Equal operator.

shape Examples

By viewing the below example, the concept of NOT EQUAL can be easily understand from the table.

mysql> select * from employee;
+--------+--------+-----------+-------+------------+
| emp_id | ename  |   job     |salary | commission |
+--------+--------+-----------+-------+------------+
|   1001 | kate   | manager   | 14000 |         10 |
|   1002 | jack   | scaleman  | 13000 |         20 |
|   1003 | maddie | business  | 14000 |         30 |
|   1004 | madd   | business  | 14000 |       NULL |
|   1005 | winni  | marketing | 14500 |       NULL |
+--------+--------+-----------+-------+------------+
5 rows in set (0.00 sec)

mysql> select * from employee  where job'business';
+--------+-------+-----------+-------+------------+
| emp_id | ename |   job     |salary | commission |
+--------+-------+-----------+-------+------------+
|   1001 | kate  | manager   | 14000 |         10 |
|   1002 | jack  | scaleman  | 13000 |         20 |
|   1005 | winni | marketing | 14500 |       NULL |
+--------+-------+-----------+-------+------------+
3 rows in set (0.00 sec)

The above example tells that, when Not Equal operation is performed on a column name job<>business then it automatically display remaining employee who are working in another sectors.

Summary

shape Key Points

  • Logical AND returns true if two conditions are true.Logical OR returns true if single condition is valid.
  • IS NOT NULL removes null values and IS NULL displays null values.
  • BETWEEN AND checks the values between the condition.
  • NOT EQUAL show all the details except the given condition.