SQL query beginner's Guide Reading Notes (4) where clause, beginner's Guide where
CHAPTER6 Filtering Your Data
This chapter introduces the WHERE clause.
Predicates
Comparison, BETWEEN, IN, LIKE, and is null.
We'll cover theother two-Quantified and EXISTS-in Chapter 11, Subqueries.
Comparison
Integrity and Inequality |
=, <> |
Less Than and Greater |
<,> |
Less than or equal |
<= |
Greater than or equal |
> = |
Combinecomparisons usingAND andOR
Range
TheBETWEEN... ANDpredicatedefines the range by using the value of the second value expression
As the start pointand the value of the third value expression as the end point.
Both the start pointand end point are part of the range.
If you are familiar with Value Expression1 <= ValueExperssion2) and (Value Expression1> = Value Experssion3), you can consider using... AND, so that SQL statements are easier to read AND understand.
Set Membership
Pattern Match
Apatternstring can consist ofany logical combination of regular string characters and
Two special wildcardcharacters: the percent sign (%) and the underscore (_).
Thepercent sign represents zero or more arbitrary regular characters, and theunderscore represents a single arbitraryregular character.
The % and _ Wildcards are similar to the * and?
What should I do if Wildcards are mixed with normal characters? For example, what if we need to match strings with underlines? This is also the obfuscation of feature characters contained in regular characters, which is often encountered in encoding. In this case, we need to ESCAPE feature characters into regular characters. We use the ESCAPE keyword to implement this function.
For example, you can see at a glance:
"Showme a list of products that have product codes beginning with 'G _ 00' and ending in a single number orletter ."
SQL SELECT ProductName, ProductCode
FROMProducts
WHEREProductCode LIKE 'G \ _ 00 _ 'escape '\'
Keep in mind thatthe character you use as an escape character shoshould not be
Part of the valuesyou're trying to retrieve.
Null
When determining whether Value Expression is NULL, do not use Value Expression = NULL. This is a small mistake.
Excluding Rows with NOT
Order of Precedence
Whenyou treat a combined set of conditions as a single unit, by definitionit becomes
Searchcondition, and you must enclose it in parentheses.
Add parentheses to avoid possible confusion.
Whenyou need to use multiple conditions, make certain that the condition thatexcludes
Most rows from theresult set is processed first so that your database can
Potentially find theanswer faster.