SQL query beginner's Guide Reading Notes (4) where clause, beginner's Guide where

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.