SQL query Beginner's Guide reading notes (iv) WHERE clauses

CHAPTER6 Filtering Your Data

This chapter describes WHERE clauses .

Predicates

Comparison,between,in, like, and is NULL.

We ' ll cover theother two-quantified and exists-in Chapter one by one, subqueries.

Comparison

Equality and inequality	=,<>
Less Than and Greater Than	<,>
Less than or equal to	<=
Greater than or equal to	>=

combinecomparisons using and and OR

Range

Thebetween ... and predicatedefines the range by using the value of thesecond value expression

As the start Pointand the value of the third value expression as the end point.

Both the start Pointand end point is part of the range.

accustomed to use (value Expression1 <= ValueExperssion2) and (value Expression1 >= Value Experssion3) of all of you classmates , you can consider using between ... and overrides , 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

Special wildcardcharacters:the percent sign (%) and the underscore (_).

Thepercent sign represents zero or more arbitrary regular characters, and theunderscore represents a sin GLE arbitraryregular character.

% with the _ wildcard characters are similar to those in regular expressions * with the ?

What to do if a wildcard is confused with normal characters Span lang= "en-US" style= "Font-family:calibri"? For example, what do we need to do to match strings that contain underscores This is also a confusing situation where functional characters are included in regular characters , encoding often encountered at this point we need to escape the function character to the regular character , we use the escape keyword to implement this function

As An example , , At a glance :

"Showme a listof products that has product codes beginning with' g_00 ' and ending in a single number or Letter. "

SQL SELECT ProductName, ProductCode

Fromproducts

Whereproductcode like ' g\_00_ ' ESCAPE ' \ '

Keep in mind thatthe character your use as a escape character should not being

Part of the Valuesyou ' re trying to retrieve.

Null

Judging Value Expression whether to NULL Please do not use the Value Expression = NULL, It's a small mistake that's often made. .

Excluding Rows with not

Order of Precedence

whenyou Treat a combined set of conditions as a single unit, by Definitionit becomes a

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 the

Most rows from Theresult set are processed first so, your database can

Potentially find theanswer faster.

SQL query Beginner's Guide reading notes (iv) WHERE clauses