SQL WHERE clause

Sometimes you want to fetch specific data from database table. This can be achieved by data filtration. Within a SELECT statement, data is filtered by specifying the search criteria with the WHERE clause. The sytanx is:

SELECT column_name1, column_name2 FROM table_name WHERE [condition];

We will use the Customer table to demonstrate you how to fetch specific data from database table with WHERE clause:

+----+------------+-------------------+-----------+-------+-------+
| id | name       | address           | city      | state | zip   |
+----+------------+-------------------+-----------+-------+-------+
|  1 | Bill Smith | 123 Main Street   | Hope      | CA    | 98765 |
|  2 | Mary Smith | 123 Dorian Street | Harmony   | AZ    | 98765 |
|  3 | Bob Smith  | 123 Laugh Street  | Hope      | CA    | 98765 |
|  4 | Chang Chao | 123 Dorian Streat | Hong Kong | CN    | 98765 |
+----+------------+-------------------+-----------+-------+-------+

We’ll start with a simple WHERE Clause, as follows:

SELECT * FROM customer WHERE city=’Hope’;

This would produce the following result:

+----+------------+------------------+------+-------+-------+
| id | name       | address          | city | state | zip   |
+----+------------+------------------+------+-------+-------+
|  1 | Bill Smith | 123 Main Street  | Hope | CA    | 98765 |
|  3 | Bob Smith  | 123 Laugh Street | Hope | CA    | 98765 |
+----+------------+------------------+------+-------+-------+

This statement retrieves all columns from the Customer table, but instead of returning all rows, only rows with a city name Hope are returned.

The WHERE clause operators

The first WHERE clause we looked at tests for equality – determining if a column contains a specific word. SQL supports a whole range of conditional operators.