Hit enter after type your search item
Home / SQL Tutorials / How to use WHERE clause in SQL?

How to use WHERE clause in SQL?

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:


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 WHERE clause with equal to (=) operator

This simple example uses the WHERE clause with the single numeric column and equal to operator i.e. ‘=’. We will get the record of an employee which ID is equal to 4 from the sto_employees table.

The Query with WHERE clause:


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 which salary is greater than 4500. See how a condition is applied with ‘>’ operator in the WHERE clause:

The Query:


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:

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:


For making sure both the conditions are true, the ‘AND’ operator is used. The AND operator enables using multiple conditions in the WHERE clause. All the conditions have to be TRUE for returning the records as using 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.

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

Using OR operator in 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 above example except I used ‘OR’ instead of ‘AND’ operator:

The query with OR in WHERE:


The resultset:

SQL WHERE OR

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

The example of using IN operator in WHERE clause

The 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 AND 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 IN clause as follows:

id IN (1, 3, 5, 7)

See the query below with IN operator:


The resultset:

SQL WHERE IN

The example of BETWEEN operator in WHERE clause

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

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


The result:

SQL WHERE BETWEEN

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:

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 single salary paid is greater than 4350.

The Query:


The full table data and resultset with above query:

SQL WHERE GROUP

Can I use WHERE clause with aggregated function?

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


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 generally used with the GROUP BY clause for filtering aggregated results.

See an example of HAVING clause below with WHERE clause:


The resultset:

SQL WHERE HAVING

The example of using WHERE clause with JOIN

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

Summary

In this tutorial, we learned how to filter resultsets in SQL (MS SQL Server and MySQL etc.) by using the WHERE clause. I showed you the 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.

This div height required for enabling the sticky sidebar