Tutorials – Filter Rows Using MySQL WHERE

mysql-where-simple

Summary: you will learn how to use MySQL WHERE clause to filter rows that return from the SELECT statement.

If you use the SELECT statement without the WHERE clause, you will get all the rows in a database table, which is a lot more information than you need. The WHERE clause allows you to specify exact rows to select based on certain criteria e.g., find all customers in the U.S.

In the following query, we get customer’s name and city from the customers table, and we only select customers in the U.S. In the WHERE clause, we compare the values of the country column with the string USA.

1
2
3
SELECT customerName, city
FROM customers
WHERE country = 'USA';

mysql-where-simple

We typically call the expression in the WHERE clause is a condition. You can form a simple condition like the query above, or a very complex one that combines multiple expressions with logical operators such as AND and OR. For example, to find all customers in the U.S . and in New York city, you use the following query:

1
2
3
4
SELECT customerName, city
FROM customers
WHERE country = 'USA' AND
           city    = 'NYC';

mysql-where-AND

You can test the condition for not only equality but also inequality. For example, to find all customers whose credit limit is greater than 200000 USD, you use the following query:

1
2
3
SELECT customerName, creditlimit
FROM customers
WHERE creditlimit > 200000;

mysql-where-greater

There are several useful operators that you can use in the WHERE clause to form practical queries such as:

  • BETWEEN selects values within a range of values.
  • LIKE matches value based on pattern matching.
  • IN specifies if the value matches any value in a list.
  • IS NULL checks if the value is NULL

The WHERE clause is used not only with the SELECT statement but also other SQL statements to filter rows such as DELETE and UPDATE.

In this tutorial, we’ve shown you how to use MySQL WHERE clause to filter records based on conditions.

From http://www.mysqltutorial.org/