Summary: in this tutorial, you will learn how to use the SQL WHERE clause with the SELECT statement to filter rows in a result set.
SQL WHERE clause syntax
The SQL WHERE clause is often used with other SQL statements such as SELECT, INSERT, UPDATE and DELETE to filter rows that does not satisfy a specific row condition. The following illustrates the syntax of the SQL WHERE clause with SELECT statement:
1 2 3 | SELECT column_list FROM table_list WHERE row_condition. |
SQL provides various operators such as comparison operators, logical operators…etc to allow you to construct row conditions.
SQL WHERE with comparison operators
The following are the comparison operators in SQL:
- = Equal
- > Greater than
- < Less than
- >= Greater than or equal
- <= Less than or equal
- <> Not equal
Suppose you want to find employee whose last name is King, you can perform the following query:
1 2 3 4 5 | SELECT lastname, firstname, title FROM employees WHERE lastname = 'King' |
How it works.
- First, SQL fetches all rows from employees table that you specify in the FROM clause.
- Second, SQL eliminates rows which do not satisfy the row condition. In this case, SQL only get rows that has value of the column lastname is King.
- Third, SQL returns only columns that are available SELECT clause: lastname, firstname and title.
To find employees who are not located in the USA, you can use the not equal operator (<>) in the WHERE clause as follows:
1 2 3 4 5 6 | SELECT lastname, firstname, title, country FROM employees WHERE country <> 'USA' |
To find employees who joined company before 1993, you can use less than operator (<) like the following query:
1 2 3 4 5 6 7 | SELECT lastname, firstname, title, country, DATE(hiredate) FROM employees WHERE hiredate < '1993-01-01' |
To find employees who joined the company after 1993, you use the greater than operator ( > ) in WHERE clause as follows:
1 2 3 4 5 6 7 | SELECT lastname, firstname, title, country, DATE(hiredate) FROM employees WHERE hiredate > '1993-01-01' |
SQL WHERE with logical operators
The logical operators in SQL are AND, OR, NOT, BETWEEN, EXISTS, IN, LIKE…etc. We are going to show you how to use AND, OR and NOT operators in this tutorial.
AND and OR operators are used to combine conditions in the WHERE clause. NOT operator reverses the result of a condition.
SQL WHERE with AND operator
The AND operator combines two conditions and returns TRUE only if both conditions are TRUE.
Suppose you want to find employees who joined the company after 1993 and are located in the USA, you can use the AND operator as follows:
1 2 3 4 5 6 7 | SELECT lastname, firstname, title, country, DATE(hiredate) FROM employees WHERE hiredate > '1993-01-01' AND country = 'USA' |
SQL WHERE with OR operator
The OR operator combines two conditions, however it returns TRUE when at least a condition is TRUE.
For example, to find employees who are located in London city or Seattle city, you can use the OR operator as follows:
1 2 3 4 5 | SELECT firstname, lastname, city FROM employees WHERE city = 'London' OR city = 'Seattle' |
SQL WHERE with NOT operator
To reverse the result of a condition, you use the NOT operator. For example, to find employees who are not located in London or Seattle city, you use the NOT operator as follows:
1 2 3 4 5 | SELECT firstname, lastname, city FROM employees WHERE NOT (city = 'London' OR city = 'Seattle') |
When you use more than one logical operator, SQL evaluates the NOT operator first, then AND operator, and finally OR operator. This is called operator precedence.
To force the order of the evaluation, you use parentheses like the query above; in this case, SQL evaluates the OR operator first and then the NOT operator.
In this tutorial, you have learned how to filter the rows in result sets by using the SQL WHERE clause with conditions.