selecting data using conditions

In this section of the MySQL tutorial we’ll look at the format of a SELECT statement we met in the last session in detail. We will learn how to use the select statement using the WHERE clause.

SELECT column_names from table_name [WHERE ...conditions];

Now, we know that the conditions are optional (we’ve seen several examples in the last session… and you would have encountered them in the assignments too).

The SELECT statement without conditions lists all the data in the specified columns. The strength of RDBMS lies in letting you retrieve data based on certain specified conditions.

In this session we’ll look at the SQL Comparision Operators.

The = and != comparision operators for MySQL Select

SELECT f_name, l_name from employee_data where f_name = 'John';

+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | Hagan      |
| John   | MacFarland |
+--------+------------+
2 rows in set (0.00 sec)

This displays the first and last names of all employees whose first names are John. Note that the word John in the condition is surrounded by single quotes. You can also use double quotes. The quotes are important since MySQL will throw an error if they are missing. Also, MySQL comparisions are case insensitive; which means "john", "John" or even "JoHn" would work!

SELECT f_name,l_name from employee_data where title="Programmer";

+--------+------------+
| f_name | l_name     |
+--------+------------+
| Fred   | Kruger     |
| John   | MacFarland |
| Edward | Sakamuro   |
| Alok   | Nanda      |
+--------+------------+
4 rows in set (0.00 sec)

Selects the first and last names of all employees who are programmers.

SELECT f_name, l_name from employee_data where age = 32;
+---------+--------+
| f_name  | l_name |
+---------+--------+
| John    | Hagan  |
| Ganesh  | Pillai |
| Alok    | Nanda  |
| Arthur  | Hoopla |
| Kim     | Hunter |
| Shahida | Ali    |
+---------+--------+
6 rows in set (0.00 sec)

This lists the first and last names of all empoyees 32 years of age. Remember that the column type of age was int, hence it’s not necessary to surround 32 with quotes. This is a subtle difference between text and integer column types.

The != means ‘not equal to’ and is the opposite of the equality operator.

The greater than and lesser than operators

Okay, let’s retrieve the first names of all employees who are older than 32.

SELECT f_name, l_name from employee_data where age > 32;
+--------+------------+
| f_name | l_name     |
+--------+------------+
| John   | MacFarland |
| Hassan | Rajabi     |
| Paul   | Simon      |
| Roger  | Lewis      |
| Danny  | Gibson     |
| Mike   | Harper     |
| Peter  | Champion   |
+--------+------------+
7 rows in set (0.00 sec)

How about employees who draw more than $120000 as salary…

SELECT f_name, l_name from employee_data where salary > 120000;
+--------+--------+
| f_name | l_name |
+--------+--------+
| Manish | Sharma |
+--------+--------+
1 row in set (0.00 sec)

Now, let’s list all employees who have had less than 3 years of service in the company.

SELECT f_name, l_name from employee_data where yos < 3;
+--------+----------+
| f_name | l_name   |
+--------+----------+
| Mary   | Anchor   |
| Edward | Sakamuro |
| Paul   | Simon    |
| Arthur | Hoopla   |
| Kim    | Hunter   |
| Roger  | Lewis    |
| Danny  | Gibson   |
| Mike   | Harper   |
| Hal    | Simlai   |
| Joseph | Irvine   |
+--------+----------+
10 rows in set (0.00 sec)

The <= and >= operators for selecting MySQL data

Used primarily with integer data, the less than equal (<=) and greater than equal (>=)operators provide additional functionality.

select f_name, l_name, age, salary
from employee_data where age >= 33;

+--------+------------+------+--------+
| f_name | l_name     | age  | salary |
+--------+------------+------+--------+
| John   | MacFarland |   34 |  80000 |
| Hassan | Rajabi     |   33 |  90000 |
| Paul   | Simon      |   43 |  85000 |
| Roger  | Lewis      |   35 | 100000 |
| Danny  | Gibson     |   34 |  90000 |
| Mike   | Harper     |   36 | 120000 |
| Peter  | Champion   |   36 | 120000 |
+--------+------------+------+--------+
7 rows in set (0.00 sec)

Selects the names, ages and salaries of employees who are more than or equal to 33 years of age..

select f_name, l_name from employee_data where yos <= 2;
+--------+----------+
| f_name | l_name   |
+--------+----------+
| Mary   | Anchor   |
| Edward | Sakamuro |
| Paul   | Simon    |
| Arthur | Hoopla   |
| Kim    | Hunter   |
| Roger  | Lewis    |
| Danny  | Gibson   |
| Mike   | Harper   |
| Hal    | Simlai   |
| Joseph | Irvine   |
+--------+----------+
10 rows in set (0.00 sec)

Displays employee names who have less than or equal to 2 years of service in the company.