More about WHERE Clause
Filters allow us to limit the number of rows returned by our queries. We set criteria to filter out rows that do not match a certain conditional statement. The SQL's WHERE clause is always will be in front of any conditions you specify. We can filter which rows will be returned when running SQL queries by using SQL's WHERE clause. This clause enables us to specify a search condition in the query to restrict the number of rows returned in the result set. Using the preceding SELECT statement example, you can filter out all products except those supplied by the supplier with the SupplierID of 2:
- SELECT *
- FROM Products
- WHERE SupplierID = 2
When this query is executed, rows in the Products table will be returned only if they have a SupplierID hat equals 2. We can limit the returned data even further by using an additional condition statement. If we do use more than one condition statement we separate the criteria by using the AND operator, as shown in the following SELECT statement:
- SELECT * FROM Products WHERE SupplierID = 2 AND UnitPrice=25
Predicates using the following operators in their selection conditions are called simple predicates:
The following query displays all rows in the SUPPLIER table containing a value of 20 in their STATUS column:
- SELECT * FROM supplier WHERE status = 20
The query results appear below:
The WHERE clause in the next example compares a character string "Dallas" with the CITY column. The query below retrieves SNO (supplier number) and STATUS column data for all suppliers in Paris:
- SELECT sno, status FROM supplier WHERE city = "Dallas"
Note that character strings can be enclosed in either double (") or single (') quotes. The display result appears below:
When one column value is compared against another column value in a query -- and the column data types are different, the XDB system tries to convert one of the data values to a data type compatible with the other data value before performing the comparison. For example, if integer and float column values are compared, the INT data value is converted to a FLOAT value before XDB performs the comparison.
Spaces in data values are considered when evaluating retrieval conditions. Right justified data with leading spaces does not equal left-justified data with trailing spaces. For example, the string "JOHN" is read as a completely different value, depending on the number of preceding or trailing blank spaces attached. The LIKE predicate operator allows string pattern matching without strict adherence to character string content and order.
All three of the < > or ! = or ^ = predicate operators specify records for retrieval that do not meet the specified condition. For example, the following query retrieves the PNO (part number) column values of all PART table records with a COLOR column value other than "RED."
- SELECT pno FROM part WHERE color != "RED"
Compound Predicate
Compound predicates use the operators OR and AND. For example, to display the SNO column values for all suppliers in "Dallas" with a STATUS column value greater than 20, use the following query:
- SELECT sno FROM supplier WHERE city = "Dallas" AND status > 20
Shown next is the same query written with the OR operator rather than the AND operator. This SELECT command statement retrieves PNO column values for those parts that are either "RED", or weigh more than 15 pounds.
- SELECT pno FROM part WHERE color = "RED" OR weight > 15
In complex queries, parentheses can be used to indicate the order of evaluation. The condition(s) surrounded by the innermost pair of parentheses is applied first.
- SELECT pno, pname FROM part WHERE color = "green" OR (city = 'Boston' AND weight < 15)
This last query retrieves PNO and PNAME column values of all parts that are either green or are both made in London, and have a weight less than 15.
The query result is displayed below:
The BETWEEN operator allows XDB to evaluate whether or not data values sort between a range of values indicated in the predicate. The format for a predicate with a BETWEEN operator is shown below:
expres1 [NOT] BETWEEN expres2 AND expres3
Each occurrence of the expression expres1 is evaluated to determine if it sorts between and including the range values indicated by expres2 and expres3 (the range values in the BETWEEN clause are also part of the result set). The three variable expressions above can consist of any combination of column data values, constants, etc. The next query selects all PART table records with WEIGHT column values between 15 and 18:
- SELECT pno, pname FROM part WHERE weight BETWEEN 15 AND 18
The following records are retrieved:
IN Operator
The IN (and NOT IN) operators cause an enumerated list of values appearing in the WHERE clause predicate to be evaluated for a true condition.
- SELECT sno, sname FROM supplier WHERE sno IN ("S1", "S2", "S3")
All data values in the SNO column must match one of the three string constants in order for their corresponding records to be retrieved. This query retrieves the following data values
IS [NOT] NULL Operator
The IS NULL operator can be used in WHERE clause predicates to match null values occurring in a specified data column. For example, the query below retrieves all PART table records that do not have a NULL value in their COLOR data column
- SELECT * FROM part WHERE color IS NOT NULL
When used in a predicate, the NULL operator keyword must be preceded with either the IS or IS NOT keywords. Operators such as =, !=, or ^= can not be used with NULL.