The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. We are going to use the Customers table from the previous chapter, to illustrate the use of the SQL WHERE command.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator value
With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns
BETWEEN Between an inclusive range
“AND” Condition
The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement – select, insert, update, or delete.
The syntax for the AND condition is:
SELECT columns
FROM tables
WHERE column1 = ‘value1’
and column2 = ‘value2’;
The AND condition requires that each condition be must be met for the record to be included in the result set. In this case, column1 has to equal ‘value1’ and column2 has to equal ‘value2’.
“OR” Condition
The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement – select, insert, update, or delete.
The syntax for the OR condition is:
SELECT columns
FROM tables
WHERE column1 = ‘value1’
or column2 = ‘value2’;The OR condition requires that any of the conditions be must be met for the record to be included in the result set. In this case, column1 has to equal ‘value1’ OR column2 has to equal ‘value2’.
Combining the “AND” and “OR” Conditions
The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement – select, insert, update, or delete. When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.
Example
The first example that we’ll take a look at an example that combines the AND and OR conditions.
SELECT *
FROM suppliers
WHERE (city = ‘New York’ and name = ‘IBM’)
or (city = ‘Newark’);
LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE patternThe LIKE condition can be used in any valid SQL statement – select, insert, update, or delete.
The patterns that you can choose from are:
% allows you to match any string of any length (including zero length)
_ allows you to match on a single character
Examples using % wildcard
The first example that we’ll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with ‘Hew’.
SELECT *
FROM suppliers
WHERE supplier_name like ‘Hew%’;
You can also using the wildcard multiple times within the same string. For example,
SELECT *
FROM suppliers
WHERE supplier_name like ‘%bob%’;
Examples using _ wildcard
Next, let’s explain how the _ wildcard works. Remember that the _ is looking for only one character.
For example,
SELECT *
FROM suppliers
WHERE supplier_name like ‘Sm_th’;
This SQL statement would return all suppliers whose name is 5 characters long, where the first two characters is ‘Sm’ and the last two characters is ‘th’. For example, it could return suppliers whose name is ‘Smith’, ‘Smyth’, ‘Smath’, ‘Smeth’, etc.
IN Function
The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…)
The following is an SQL statement that uses the IN function:
SELECT *
FROM suppliers
WHERE supplier_name in ( ‘IBM’, ‘Hewlett Packard’, ‘Microsoft’);
This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.
It is equivalent to the following statement:
SELECT *
FROM suppliers
WHERE supplier_name = ‘IBM’
OR supplier_name = ‘Hewlett Packard’
OR supplier_name = ‘Microsoft’;
As you can see, using the IN function makes the statement easier to read and more efficient.
BETWEEN
The BETWEEN condition allows you to retrieve values within a range.
The syntax for the BETWEEN condition is:
SELECT columns
FROM tables
WHERE column1 between value1 and value2;
This SQL statement will return the records where column1 is within the range of value1 and value2 (inclusive). The BETWEEN function can be used in any valid SQL statement – select, insert, update, or delete.
The following is an SQL statement that uses the BETWEEN function:
SELECT *
FROM suppliers
WHERE supplier_id between 5000 AND 5010;
This would return all rows where the supplier_id is between 5000 and 5010, inclusive. It is equivalent to the following SQL statement:
SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;
EXISTS Condition
The EXISTS condition is considered “to be met” if the subquery returns at least one row.
The syntax for the EXISTS condition is:
SELECT columns
FROM tables
WHERE EXISTS ( subquery );
The EXISTS condition can be used in any valid SQL statement – select, insert, update, or delete.
Example1:
Let’s take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:
SELECT *
FROM suppliers
WHERE EXISTS (select * from orders where suppliers.supplier_id = orders.supplier_id);
This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.
Example 2: NOT EXISTS
The EXISTS condition can also be combined with the NOT operator.
For example,
SELECT * FROM suppliers WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);
This will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.
Example 3: DELETE Statement
The following is an example of a delete statement that utilizes the EXISTS condition:
DELETE FROM suppliers WHERE EXISTS (select * from orders where suppliers.supplier_id = orders.supplier_id);
Recent Comments