SQL WHERE Condition

Imagine you have thousands of customers. You want to get only the phone number of customers whose last name is “Doe”.

The syntax to do that is the following:

select phone_number
from clients
where last_name=’Doe’

In that query:

  • the condition is: where last_name=’Doe’.
  • only rows for which the value of column last_name equals “Doe” will be displayed.

I don’t remember the exact value of the customer’s name. I just remember it starts with “ra”. What should I do?

In that case, the syntax to find your customer is the following:

select first_name, last_name, phone_number
from clients
where last_name like ‘ra%

This will display a list of customers which name starts with “ra”.

Example:

  • rao.
  • ramada.

Hopefully, looking at the names displayed, you will remember which customer you were looking for.

Can I use something else than “ra”?

Yes, absolutely. You can use any group of letters, numbers and special characters of any size instead of “ra”.

What if I remember only the last letters of the name?

Put the sign “%” before the letters you remember. Example: we remember that the customer’s last name ends with “ao”. Then the syntax is the following:

select phone_number
from clients
where last_name like %ao’

This will display a list of customers which name ending with “ao”.

Example:

  • rao.
  • matao.

I just remember a few letters contained in the name: “ao”. I don’t remember if it is in the beginning, in the middle or in the end.

In that case, you put a “%” before and after the letters you remember:

select first_name, last_name, phone_number
from clients
where last_name like '%ao%'

Example of names that would match (they all contain “ao”):

  • rao
  • matao
  • taora
  • groaora
  • oarat

I want to display two clients in one query, one with the name ‘rao’ and another with the name ‘matao’

In that case, use the keyword “in” followed by a list of values. The where condition will be fulfilled if the value of the column is equal to one of these values. Here is the syntax if you want to display these two clients’ phone number:

select phone_number from clients
where last_name in ("rao","matao")

I remember the first and second character of the name but I don’t remember the second one.

In that case you use the following syntax:

select first_name,last_name from clients where last_name like "r_o%"

"_" can be any character, but can be only one character. As the result:

  • following will be found : rao, riot
  • following will not be found: riao
  • In this example, you knew the first and second character (one unknown character in between). If you know the first and third character (two unknown character in between), then you would just need to use the underscore ("_") twice:

    select first_name,last_name from clients where last_name like "r__o%"

    What if the condition uses numbers instead of text?

    In case the value of the column used in the condition is a number, you can use the following operators:

    • Column value is equal to … :        
    • column value is strictly greater than (cannot be equal) … :        >    
    • column value is strictly smaller than (cannot be equal) … :        <  
    • column value is greater or equal to … :        >=  
    • column value is smaller or equal to … :        <=
    • column value is between x and y (x and y are numbers): between x and y
    • column value not equal to x: <> x

    Example 1: we want to show salesmen for which the total sales is equal to or greater than 100 000 dollars. The syntax is the following:

    select first_name, last_name
    from salesperson
    where total_sales >= 100000

    Example 2: we want to show salesmen for which the total sales is strictly smaller than 100 000 dollars (can be less than 100 000 but cannot be equal to 100 000). The syntax is the following:

    select first_name, last_name
    from salesperson
    where total_sales < 100000

     

    To test your understanding of this lesson, take the SQL Where Condition Quiz or practice SQL where against a real database, practice where statement on numbers, practice select with like. You can also go to the next lesson about combining conditions in SQL
 
Share on:


Author:
Last udpated: September 10, 2017
Created: June 22, 2014