SQL WHERE Clause

SELECT * FROM tbl_name

Where name = ‘Hello World’;

An example of a WHERE clause with equal to (=) operator

This simple example uses the WHERE clause with a single numeric column and equal to operator i.e. ‘=’.

We will get the record of an employee whose ID is equal to 4 from the sto_employees table.

The Query with WHERE clause:

SELECT id, emp_name, emp_salary FROM sto_employees

WHERE id = 4;

The result:

The example of using greater than ‘>’ operator

Now, we will retrieve the records from the same table for those employees whose salary is greater than 4500.

See how a condition is applied with the ‘>’ operator in the WHERE clause:

The Query:

SELECT id, emp_name, emp_salary FROM sto_employees

WHERE emp_salary > 4500;

The resultset:

Using a VARCHAR column for filtering results

For specifying the values for the numeric columns, you saw we used values without quotes in the WHERE clause. However, for text columns, you have to use single quotes for enclosing a value.

See this example for fetching the record of an employee ‘Ben’:

The query:

SELECT id, emp_name, emp_salary,join_date FROM sto_employees

WHERE emp_name = 'Ben';

Using multiple conditions in the WHERE clause example

In this example, I have used two conditions in the WHERE clause and both of these have to be TRUE in order to fetch records from the table.

• First condition: Employee Name = ‘Mike’
• Second Condition: Employee Salary > 5100

The Query:

SELECT id, emp_name, emp_salary,join_date FROM sto_employees

WHERE emp_name = 'Mike' AND emp_salary > 5100;

To make sure both the conditions are true, the ‘AND’ operator is used.

In our table, we have two records for the employee name ‘Mike’, however, the query returned only one record as one ‘Mike’ has 5000.5 salary.

Using the OR operator in the WHERE clause example

You may also use multiple conditions and column names by using the OR operator.

See the same query as in the above example except I used the ‘OR’ instead of the ‘AND’ operator:

The query with OR in WHERE:

SELECT id, emp_name, emp_salary,join_date

FROM sto_employees

WHERE emp_name = 'Mike' OR emp_salary > 5100;

The resultset:

The records of ‘Mike’ as well as those whose salary is greater than 5100 are also fetched in this query because the ‘OR’ operator requires any of the conditions to be TRUE.

The example of using the IN operator in the WHERE clause

SQL IN operator is the shorthand for several OR operators for the same column.

For example, we want to return the records of employees with ID = 1,3,5,7. We may use the OR operator in the WHERE clause as follows:

The same can be done by using the IN operator as follows:

See the query below with the IN operator:

SELECT id, emp_name, emp_salary,join_date FROM sto_employees

WHERE id IN (1, 3, 5, 7);

The resultset:

The example of BETWEEN operator in the WHERE clause

See the query below to learn how to use BETWEEN operator in the WHERE clause:

SELECT id, emp_name, emp_age, emp_salary,join_date FROM sto_employees

WHERE emp_age BETWEEN 30 AND 40;

The result:

The values used in the BETWEEN operator are inclusive.

The example of WHERE with ORDER BY clause

The ORDER BY clause sorts the recordset in ascending or descending order. You may also use this in the query that uses the WHERE clause.

The ORDER BY clause is always used after the WHERE clause in the SELECT statement. See a demo below:

SELECT emp_name, emp_age, emp_salary,join_date FROM sto_employees

WHERE emp_salary < 6000

ORDER BY emp_age;

The example of WHERE with GROUP BY clause

You may also use the WHERE clause in aggragated queries; that use aggregated functions like SUM, COUNT, MAX, etc. and GROUP BY clause.

The WHERE clause is used before the GROUP BY clause. See an example query below where we will get the SUM of salaries paid to each employee and the single salary paid is greater than 4350.

The Query:

SELECT emp_name, SUM(emp_sal_paid) As "Total Paid"

FROM sto_emp_salary_paid

WHERE emp_sal_paid > 4350

GROUP BY emp_name;

The full table data and resultset with the above query:

Can I use WHERE clause with an aggregated function?

No, the WHERE clause cannot be used with aggregated functions. For example, this query will produce an error:

SELECT emp_name, SUM(emp_sal_paid) As "Total Paid"

FROM sto_emp_salary_paid

WHERE SUM(emp_sal_paid) > 4350

GROUP BY emp_name;

So, what if you require filtering the results based on aggregated data?

The answer is using the HAVING clause.

See an example of a HAVING clause below with the WHERE clause:

SELECT emp_name, SUM(emp_sal_paid) As "Total Paid"

FROM sto_emp_salary_paid

WHERE emp_sal_paid > 4350

GROUP BY emp_name

HAVING SUM(emp_sal_paid) >5000;

The resultset:

The example of using a WHERE clause with JOIN

The final example regarding the usage of WHERE clause shows SQL JOIN and WHERE clauses in action:

SELECT * FROM sto_employees
JOIN sto_orders  ON sto_employees.id=sto_orders.emp_id 
WHERE emp_name IN ('Mike', 'Jimmy');

Summary