SQL WHERE Clause

A visual guide to mastering the WHERE clause with logical and comparison operators for precise data filtering. See examples of Comparison, Logical, operators along with IN and BETWEEN operators.

What is SQL WHERE clause?

The simple SELECT statement returns all records from the table(s) in an SQL database. For example:

SELECT * FROM tbl_name;

In order to return only filtered records or rows that fulfill certain criteria, the SQL WHERE clause is used in the SELECT statement.

For example:

SELECT * FROM tbl_name

Where name = ‘Hello World’;

The WHERE clause is also used with the UPDATE and DELETE commands for affecting only limited rows.

The section below shows using the WHERE clause with different operators like =, <>, <, >, AND, OR etc. – so keep reading.

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:

SQL WHERE equal-to

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:

SQL WHERE greater

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.

The AND operator enables the use of multiple conditions in the WHERE clause. All the conditions have to be TRUE for returning the records while using the ‘AND’ operator.

You may also use more than two conditions by adding the ‘AND’ operator.

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.

The OR operator returns the records if any of the condition is TRUE.

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:

SQL WHERE OR

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:

id = 1 OR id = 3 OR id = 5 OR id = 7

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

id IN (1, 3, 5, 7)

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:

SQL WHERE IN

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:

SQL WHERE BETWEEN

The values used in the BETWEEN operator are inclusive.

The BETWEEN operator returns the records for the specified range of values. You may use numbers, strings, etc. in the BETWEEN operator.

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:

SQL WHERE GROUP

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.

The HAVING clause can be used with aggregated functions and is generally used with the GROUP BY clause for filtering aggregated results.

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:

SQL WHERE HAVING

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

  • In this tutorial, we learned how to filter resultsets in SQL (MS SQL Server and MySQL, etc.) by using the WHERE clause.
  • We saw examples of various operators (=, >, IN, BETWEEN, etc.) along with different clauses (GROUP BY, ORDER BY, JOIN), etc.
  • The tutorial also covered the aspect of filtering the records based on the aggregated function by using the HAVING clause.
Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!