In previous chapter you saw how we can select data from the table. But it was pretty straightforward selection of data, what if we require getting only some set of data that is based on some conditions. The data should satisfy the conditions specified by you. This is used to pass on data to programs which give out the report. You can create conditions using the operators (check the operators chapter).
Syntax:
SELECT * FROM table_name WHERE CONDITION; |
Example 1:
Consider our Employee table.
+-------------------+-------------------+----------+------------------------+-------------+-------------+ | EMP_ID | EMMP_NAME | AGEG | PHONE_NUM | DEPT_ID | SALARY | +-------------------+-------------------+----------+------------------------+-------------+-------------+ | 1 | john | 35 | 100233023 | 2 | 10000 | | 2 | Linda | 30 | 100234565 | 1 | 15000 | | 3 | Max | 40 | 122222344 | 3 | 22000 | | 4 | Will | 40 | 12323424 | 3 | 31000 | | 5 | Michal | 45 | 12323434 | 3 | 5000 | +-------------------+-------------------+----------+------------------------+-------------+-------------+
Retrieve only the records with dept_id = 3.
mysql> select * from employee where dept_id = 3; +------------+------------------------+----------+--------------------+-------------+ | EMP_ID | EMMP_NAME | AGEG | PHONE_NUM | DEPT_ID | +------------+------------------------+----------+--------------------+-------------+ | 3 | Max | 40 | 122222344 | 3 | | 4 | Will | 40 | 12323424 | 3 | | 5 | Michal | 45 | 12323434 | 3 | +------------+------------------------+----------+--------------------+-------------+ 3 rows in set (0.00 sec)
Suppose we have one column as SALARY and want to retrieve records based on that.
Example 2:
If we want to get the records with salary > 10000 then we should get result set of 3 records.
mysql> SELECT * FROM EMPLOYEE WHERE SALARY > 10000; +------------+---------------------+---------+--------------------+-------------+--------------+ | EMP_ID | EMMP_NAME | AGEG | PHONE_NUM | DEPT_ID | SALARY | +------------+---------------------+---------+--------------------+-------------+--------------+ | 2 | Linda | 30 | 100234565 | 1 | 15000 | | 3 | Max | 40 | 122222344 | 3 | 22000 | | 4 | Will | 40 | 12323424 | 3 | 31000 | +------------+---------------------+---------+--------------------+-------------+--------------+
Example 3:
Now if we want to know the employee details that have salary ranging from 10000 to 30000. Then we should get result set of 3 records:
mysql> SELECT * FROM EMPLOYEE -> WHERE SALARY >= 10000 AND SALARY <30000; +------------+---------------------+------------+-------------------+ | EMP_ID | EMMP_NAME | AGEG | PHONE_NUM | DEPT_ID | SALARY | +------------+---------------------+------------+-------------------+ | 1 | john | 35 | 100233023 | 2 | 10000 | | 2 | Linda | 30 | 100234565 | 1 | 15000 | | 3 | Max | 40 | 122222344 | 3 | 22000 | +------------+---------------------+------------+-------------------+ 3 rows in set (0.03 sec)
Example 4:
Now if we want to know the employee details that have salary ranging from 10000 to 30000. This can be achieved using > and < operators as above or by using BETWEEN command.
BETWEEN is an inclusive operator meaning the value1 and value2 are included in result.
mysql> SELECT * FROM EMPLOYEE -> WHERE SALARY BETWEEN 10000 AND 30000; +------------+---------------------+------------+-------------------+----------------+-------------+ | EMP_ID | EMMP_NAME | AGEG | PHONE_NUM | DEPT_ID | SALARY | +------------+---------------------+------------+-------------------+----------------+-------------+ | 1 | john | 35 | 100233023 | 2 | 10000 | | 2 | Linda | 30 | 100234565 | 1 | 15000 | | 3 | Max | 40 | 122222344 | 3 | 22000 | +------------+---------------------+------------+-------------------+----------------+-------------+ 3 rows in set (0.03 sec)
Example 5:
Get the details of employees that are not in salary range of 5000 to 22000.
SELECT * FROM EMPLOYEE WHERE SALARY NOT BETWEEN 5000 AND 22000; +-------------------+-------------------+----------+------------------------+-------------+-------------+ | EMP_ID | EMMP_NAME | AGEG | PHONE_NUM | DEPT_ID | SALARY | +-------------------+-------------------+----------+------------------------+-------------+-------------+ | 4 | Will | 40 | 12323424 | 3 | 30000 | +-------------------+-------------------+----------+------------------------+-------------+-------------+