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:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

2 questions on “SQL WHERE Condition

  1. when doing the practice lesson for the (statement on numbers) it asks for a query where the data has a revenue value different from a number. I do not see in the lesson where it states how to write a query where a number is not equal to. I understand, =, >, , <= and between two values but what about 'different from'? Did I miss it somewhere in the lesson so far? Thank you

    • Hello Robin,

      You were right, it was not explained in the lesson. I updated this lesson on SQL where condition accordingly thanks to your comment. The syntax is <> . I am sorry I could not answer your comment earlier. I am starting to be flooded with spam comments. Now I am going to answer comments regularly again.

      Vincent


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