SQL Where

In this tutorial you will learn how to select specific records from a table using SQL.

## Selecting Record Based on Condition
In the previous chapter we've learnt how to fetch all the records from a table or table columns. But, in real world scenario we generally need to select, update or delete only those records which fulfill certain condition like users who belongs to a certain age group, or country, etc.

The `WHERE` clause is used with the SELECT, UPDATE, and DELETE. However, you'll see the use of this clause with other statements in upcoming chapters.

## Syntax
The `WHERE` clause is used with the `SELECT` statement to extract only those records that fulfill specified conditions. The basic syntax can be given with:

```sql
SELECT column_list FROM table_name WHERE condition;
```
Here, column_list are the names of columns/fields like name, age, country etc. of a database table whose values you want to fetch. However, if you want to fetch the values of all the columns available in a table, you can use the following syntax:

```sql
SELECT * FROM table_name WHERE condition;
```
Now, let's check out some examples that demonstrate how it actually works.

Suppose we've a table called employees in our database with the following records:

```
+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |   5000 |       4 |
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
|      5 | Martin Blank | 2008-06-24 |   5600 |    NULL |
+--------+--------------+------------+--------+---------+
```

### Filter Records with WHERE Clause
The following SQL statement will returns all the employees from the employees table, whose salary is greater than 7000. The `WHERE` clause simply filtered out the unwanted data.

```sql
SELECT * FROM employees
WHERE salary > 7000;
```
After execution, the output will look something like this:

```sql
+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |       5 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |       3 |
+--------+--------------+------------+--------+---------+
```
As you can see the output contains only those employees whose salary is greater than 7000. Similarly, you can fetch records from specific columns, like this:

```sql
SELECT emp_id, emp_name, hire_date, salary
FROM employees
WHERE salary > 7000;
```
After executing the above statement, you'll get the output something like this:

```
+--------+--------------+------------+--------+
| emp_id | emp_name     | hire_date  | salary |
+--------+--------------+------------+--------+
|      3 | Sarah Connor | 2005-10-18 |   8000 |
|      4 | Rick Deckard | 2007-01-03 |   7200 |
+--------+--------------+------------+--------+
```
The following statement will fetch the records of an employee whose employee id is 2.

```sql
SELECT * FROM employees
WHERE emp_id = 2;
```
This statement will produce the following output:

```
+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      2 | Tony Montana | 2002-07-15 |   6500 |       1 |
+--------+--------------+------------+--------+---------+
```
This time we got only one row in the output, because emp_id is unique for every employee.

## Operators Allowed in WHERE Clause
SQL supports a number of different operators that can be used in WHERE clause, the most important ones are summarized in the following table.

| Operator | Description                     | Example                        |
|----------|---------------------------------|--------------------------------|
| =        | Equal                            | WHERE id = 2                   |
| &gt;     | Greater than                      | WHERE age &gt; 30              |
| &lt;     | Less than                         | WHERE age &lt; 18              |
| &gt;=    | Greater than or equal             | WHERE rating &gt;= 4           |
| &lt;=    | Less than or equal                | WHERE price &lt;= 100          |
| LIKE     | Simple pattern matching           | WHERE name LIKE 'Dav'          |
| IN       | Check whether a specified value matches any value in a list or subquery | WHERE country IN ('USA', 'UK') |
| BETWEEN  | Check whether a specified value is within a range of values             | WHERE rating BETWEEN 3 AND 5   |