The WHERE clause lets you add further conditions on your query. We will see the use of WHERE clauses along with other clauses that will help us refine our queries in this section.

Commonly asked questions based on concepts in this page:

1. Give the basic usage of NOT & IN in WHERE clause?
2. Can we compare null as WHERE id=NULL?
3. Does range include endpoints when you use BETWEEN and AND in where clause?
4. What are the wildcards that you can use with LIKE and NOT LIKE? How do you escape if your result can have them?
5. Give the basic use of HAVING? When is it used? Can we use it along with WHERE?

Basic Concepts – WHERE Clause and other clauses

1. The WHERE clause lets you add further conditions on your query.

SELECT * FROM Employees WHERE firstname = ‘Heartin’;

2. Use IN clause to specify a set of acceptable values in where clause.

SELECT * FROM mytable WHERE col1 IN(“value1”,”value2”);

3. Use AND/OR to combine clauses, and NOT to negate conditions - AND insist all clauses to be true. OR insist any clause to be true.

SELECT * FROM mytable WHERE col1 NOT IN (“value2”,”value3”);

4. You can use BETWEEN and AND to filter result. The range include endpoints when you use BETWEEN and AND in where clause.

5. The comparison operators that we can use in a WHERE clause are:

·       = (Equality)

·       <> or != (Not equal to)

·       < 

·       <=

·       !< (Not less than)

·       > 

·       >=

·       !>

6. SQL don’t do comparisons involving NULL like ‘WHERE id=NULL’. Instead we can use ‘IS NULL’ or ‘IS NOT NULL’.

7. We can use LIKE to filter queries based on similarity. The wildcards that you can use with LIKE and NOT LIKE are:

• ‘-’ (Underscore) -> Stands for any one character
• ‘%’ -> Stands for any one or more characters.

Below query uses LIKE to find all employees whose first names had 4 characters:

SELECT * FROM Employees WHERE firstname LIKE ‘_ _ _ _’;

To search for terms that include the ‘_’ or ‘%’ characters using LIKE, you can escape those characters. You can put any character of your choice before that and tell SQL that character is an escape character using ESCAPE ‘#’ at the end or use backslash to escape.

SELECT Savings FROM Financials WHERE Savings LIKE ‘10#%’ ESCAPE ‘#’;

SELECT Savings FROM Financials WHERE Savings LIKE ‘10\%’;

The SIMILAR TO clause is similar to LIKE, but we can use regular expressions. Some DBMSs use REGEXP or REGEXP_LIKE instead of SIMILAR TO.

MATCH with WHERE lets you match individual records.

… WHERE (‘heartin’,’jacob’) MATCH (SELECT firstname, lastname FROM Employees);

8. The ALL keyword makes SQL checks all results returned from a sub-query. ANY means any result. ‘SOME’ means the same as ‘ANY’.

… WHERE id> ALL(SELECT Supervisor FROM Employees)

9. We can filter groups using HAVING clause when using GROUP BY with WHERE. When used together, rule is that the GROUP BY clause must come only after the WHERE clause, but can come before HAVING clause. So HAVING is required with GROUP BY when you want to apply some condition after grouping.

WHERE condition GROUP BY columnName HAVING condition