Sql server tutorial 09 Using Predicates - XADAT.COM
15799
post-template-default,single,single-post,postid-15799,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 09 Using Predicates No ratings yet.

Sql server tutorial 09 Using Predicates

Key Skills & Concepts

  • Compare SQL Data
  • Return Null Values
  • Return Similar Values
  • Reference Additional Sources of Data
  • Quantify Comparison Predicates

Up to this point in the book, I have presented a great deal of information about various
aspects of database objects and the data they store. In relation to this, I discussed
querying data (Chapter 7) and modifying data (Chapter 8). Now I want to take a step back
and focus on one aspect of these discussions: the WHERE clause. The WHERE clause, as
you might recall, allows you to specify a search condition that filters out those rows that
you do not want returned by a SELECT statement or modified by an UPDATE or
DELETE statement. The search condition includes one or more predicates that each state a
fact about any row that is to be returned or modified. SQL supports a number of types of
predicates, all of which allow you to test whether a condition is true, false, or unknown. In
this chapter, I focus on those predicates that are most commonly used by SQL
programmers, and I provide examples of how they’re used to view and modify data in an
SQL database.

Compare SQL Data

The first types of predicates that I plan to discuss are those that compare data. These
predicates, like any predicate, are included in the WHERE clause. You can include a
WHERE clause in a SELECT, UPDATE, or DELETE statement, and in each case, the
clause can contain one or more comparison predicates.

Each predicate in the WHERE clause (whether a comparison predicate or another type)
is evaluated on an individual basis to determine whether it meets the condition defined by
that predicate. After the predicates are evaluated, the WHERE clause is evaluated as a
whole. The clause must evaluate to true in order for a row to be included in a search result,
be updated, or be deleted. If the clause evaluates to false or unknown, the row is not
included or is not modified. For a complete discussion of how predicates and the WHERE
clause are evaluated, see Chapter 7.

A comparison predicate is a type of predicate that compares the values in a specified
column to a specified value. A comparison operator is used to compare those values. You
have already seen a number of comparison operators (and, subsequently, comparison
predicates) throughout the book. Table 9-1 lists the six comparison operators supported by
SQL and provides an example of each one.

Table 9-1 SQL Comparison Operators
You no doubt recognize several of these operators, and even those you don’t recognize
should be fairly self-explanatory. But let’s take a quick look at the examples in Table 9-1
to make sure you understand how a comparison predicate works. In the first row in the
table (the Equal to row), the example predicate is IN_STOCK = 47. If this were to appear
in a WHERE clause, it would look like the following:

IN_STOCK is the name of the column in the table identified in the statement that
contains the WHERE clause. The equals sign (=) is the comparison operator that is used to
compare the values in the IN_STOCK column to the value to the right of the equals sign,
which in this case is 47. Therefore, for a row to be evaluated to true, the IN_STOCK value
for that row must be 47. All six comparison operators work in the same way. In each case,
the WHERE clause must evaluate to true in order for the row to be returned in the query
results or to be modified.

While it is traditional to place the column name to the left of the comparison operator
and the constant value to the right, you can reverse them and form an equivalent
statement, assuming you also adjust the comparison operator if needed.

You no doubt recognize several of these operators, and even those you don’t recognize
should be fairly self-explanatory. But let’s take a quick look at the examples in Table 9-1
to make sure you understand how a comparison predicate works. In the first row in the
table (the Equal to row), the example predicate is IN_STOCK = 47. If this were to appear
in a WHERE clause, it would look like the following:
WHERE IN_STOCK = 47
IN_STOCK is the name of the column in the table identified in the statement that
contains the WHERE clause. The equals sign (=) is the comparison operator that is used to
compare the values in the IN_STOCK column to the value to the right of the equals sign,
which in this case is 47. Therefore, for a row to be evaluated to true, the IN_STOCK value
for that row must be 47. All six comparison operators work in the same way. In each case,
the WHERE clause must evaluate to true in order for the row to be returned in the query
results or to be modified.

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

No Comments

Sorry, the comment form is closed at this time.