SQL WHERE

SELECT column_list
FROM table_list
WHERE row_condition.

SQL WHERE clause is used with other SQL statements such as SQL SELECT, SQL DELETE and SQL UPDATE statements to filter records in the database tables which satisfy  specific row conditions. To construct row conditions, SQL provides you various operators to allow you to do so. Here are basic operators in SQL.

Suppose you want to find all employees who has last name is King, you can perform this query

SELECT lastname, firstname, title
FROM employees
WHERE lastname = 'King'

lastname  firstname  title               
--------  ---------  --------------------
King      Robert     Sales Representative

SQL first fetches all rows from employees table which specified in the FROM clause. Next SQL eliminates rows which do not satisfy the row condition, in this case rows which has lastname column is not equal to ‘King’. Then SQL eliminates all columns which do not in the column list (or sometimes refer as selection list).

To find all employees who do not live in US, you can use not equal operator (<>) in WHERE clause as follows:

SELECT lastname, firstname, title, country
FROM employees
WHERE country <> 'USA'

lastname   firstname  title                 country
---------  ---------  --------------------  -------
Buchanan   Steven     Sales Manager         UK     
Suyama     Michael    Sales Representative  UK     
King       Robert     Sales Representative  UK     
Dodsworth  Anne       Sales Representative  UK     

To find all employees who were hired before 1993, you can use less than operator ( < ) like this:

SELECT lastname, firstname, title, country,date(hiredate)
FROM employees
WHERE hiredate < '1993-01-01'

lastname   firstname  title                  country  date(hiredate)
---------  ---------  ---------------------  -------  --------------
Davolio    Nancy      Sales Representative   USA      1992-05-01    
Fuller     Andrew     Vice President, Sales  USA      1992-08-14    
Leverling  Janet      Sales Representative   USA      1992-04-01    

To find all employees who were hired after 1993, you just use the greater than operator ( > ) in WHERE clause.

SELECT lastname, firstname, title, country,date(hiredate)
FROM employees
WHERE hiredate > '1993-01-01'

lastname   firstname  title                     country  date(hiredate)
---------  ---------  ------------------------  -------  --------------
Peacock    Margaret   Sales Representative      USA      1993-05-03    
Buchanan   Steven     Sales Manager             UK       1993-10-17    
Suyama     Michael    Sales Representative      UK       1993-10-17    
King       Robert     Sales Representative      UK       1994-01-02    
Callahan   Laura      Inside Sales Coordinator  USA      1994-03-05    
Dodsworth  Anne       Sales Representative      UK       1994-11-15    

The logical operators such as OR and AND are used to combine multiple conditions in WHERE clause.

SQL WHERE with AND operator

Multiple conditions are joined by AND operator must be TRUE for entire condition evaluate to TRUE. If one of condition is false, the entire condition evaluates to FALSE as well. If one of condition is unknown or NULL the entire condition evaluates to UNKNOWN. (You will learn how to deal with NULL in the next tutorials later).

Suppose you want to find all employees who were hired after 1993 and lived in UK, you can use AND operator to combine both conditions like the following query:

SELECT lastname, firstname, title, country, date(hiredate)
FROM employees
WHERE hiredate > '1993-01-01' AND country = 'USA'

lastname  firstname  title                     country  date(hiredate)
--------  ---------  ------------------------  -------  --------------
Peacock   Margaret   Sales Representative      USA      1993-05-03    
Callahan  Laura      Inside Sales Coordinator  USA      1994-03-05    

SQL WHERE with OR operator

Multiple conditions joined by OR operator must be FALSE for entire condition is evaluated to FALSE. If one of condition is TRUE, the entire condition is evaluated to TRUE. If one of condition is UNKNOWN or NULL the entire condition is evaluated to UNKNOWN or NULL.

For example you can find all employees who live in London or Seattle city, you can use OR operator in this case.

SELECT firstname, lastname, city
FROM employees
WHERE city = 'London' OR city  = 'Seattle'

firstname  lastname   city   
---------  ---------  -------
Nancy      Davolio    Seattle
Steven     Buchanan   London 
Michael    Suyama     London 
Robert     King       London 
Laura      Callahan   Seattle
Anne       Dodsworth  London 

In this tutorial, you’ve learned how to filter the records in the result set in the SQL SELECT statement by using the SQL WHERE clause with conditions. You’ve also learned how to use various common basic operators to construct a condition and use logical operators to combine condition together. In the next tutorial you will learn how to use other operator such as SQL BETWEEN and SQL IN to retrieve data in a range of values and in a set.