WHERE SQL operator

WHERE SQL operator is used to filter results and apply conditions in SELECT, INSERT, UPDATE or DELETE.

Syntax for WHERE statement in SQL

WHERE conds;

where:

• Conds – to be met for selecting records

There is one condition in the WHERE operator

It is difficult to explain the syntax for WHERE SQL statement, so let’s start with an example where WHERE is used to apply condition 1.

In this example, we have a table with the following data:

Enter the following SQL statement:

SELECT *
   FROM suppls
  WHERE state_id = 'California';

Three records will be selected. Here are the results that you should get.

In this example, we used the WHERE SQL statement to filter the results from the suppliers table. The above SQL statement would return all rows from the suppls table, where state is ‘California’. Since SELECT uses *, all fields from the suppliers table would be displayed in the results set.

Two conditions in the WHERE operator (condition AND)

You can use the AND condition in the WHERE operator to specify more than 1 condition that must be met to select a record. Let’s look at how this can be done.

In this example, we have a table with the following data:

Now enter the following SQL statement:

SELECT *
   FROM customs
 WHERE fav_website = 'google.com'
   AND custom_id > 6000;

1 record will be selected. Here are the results that you should get:

In this example, the WHERE operator is used to define several conditions. In this case, this SQL statement uses the AND condition to return all customers whose fav_website is google.com and whose custom_id is over 6000.

Two conditions in the WHERE statement (condition OR)

You can use the OR condition in WHERE to test several conditions under which a record is returned if any of the conditions are met.

In this example, we have a product table with the following data:

Now enter the following SQL statement:

SELECT *
    FROM prods
 WHERE prod_name = 'Pear'
    OR prod_name = 'Apple';

The records will be selected. Here are the results you should get.

In this example, the WHERE operator is used to define several conditions, but instead of using the AND condition, the OR condition is used. In this case, this SQL query will return all records from the products table where prod_name is Pear or Apple.

Combining the AND and OR conditions

You can also combine the AND condition with the OR condition to check more complex conditions.

Let’s use the products table again for this example:

Now enter the following SQL operator:

SELECT *
     FROM prods
  WHERE (prod_id > 3 AND cat_id = 75)
    OR (prod_name = 'Pear');

The records will be selected. Here are the results you should get.

In this example, all records of the products table with prod_id greater than 3 and cat_id greater than 75, as well as records with prod_name being ‘Pear’, are returned.

The brackets define the order in which the AND and OR conditions are evaluated.

WHERE clause in SQL Server

WHERE clause in SQL Server, and working with OR, AND, LIKE, BETWEEN, IN, NOT IN operators