The NOT operator’s important characteristics are: To test for nulls, see “Testing for Nulls with IS NULL” later in this chapter. The null causes the result of both of the OR conditions to be unknown, so the row is excluded from the result. But the row for publisher P03 (located in Germany) is missing because it contains a null in the column state. You might expect the result, Figure 4.25, to display all the rows in the table publishers. Listing 4.25 shows the effect of nulls in conditions. Publisher P03 is missing because its state is null. OR (state 'CA') Figure 4.25 Result of Listing 4.25. This example is contrived to show the effect of nulls in conditions see Figure 4.25 for the result. List the publishers that are located in California or are not located in California. au_fname au_lname city stateĬhristian Kells New York NY Listing 4.25. OR (city = 'San Francisco') Figure 4.24 Result of Listing 4.24. List the authors who live in New York State, Colorado, or San Francisco. See Listings 4.24 and 4.25, and Figures 4.24 and 4.25, for some OR examples. You can enclose one or both of the conditions in parentheses.Like AND, OR is commutative the order in which you list the conditions doesn’t matter.OR will retrieve rows that match any condition or all the conditions. Any number of conditions can be connected with ORs.OR connects two conditions and returns true if either condition is true or if both conditions are true.The OR operator’s important characteristics are: Remember that the results of string comparisons depend on the DBMS’s collating sequence see “Sorting Rows with ORDER BY” earlier in this chapter. SELECT title_name, type, priceĪND au_lname 'CA' Figure 4.23 Result of Listing 4.23. List the biographies that sell for less than $20. See Listings 4.22 and 4.23, and Figures 4.22 and 4.23, for some AND examples. Some compound conditions need parentheses to force the order in which conditions are evaluated. AND is commutative (independent of order): WHERE condition1 AND condition2 is equivalent to WHERE condition2 AND condition1.All the conditions must be true for the row to be included in the result. Any number of conditions can be connected with ANDs.This type of table is called a truth table. The table’s left column shows the truth values of the first condition, the top row shows the truth values of the second condition, and each intersection shows the AND outcome. Table 4.3 shows the possible outcomes when you combine two conditions with AND.AND connects two conditions and returns true only if both conditions are true.The AND operator’s important characteristics are: (To retrieve rows with nulls, see “Testing for Nulls with IS NULL” later in this chapter.) The AND operator If the result of a compound condition is false or unknown, the row is excluded from the result. In three-value logic, the result of a logical expression is true, false, or unknown. Databases model real data, however, and our knowledge of the world is imperfect-that’s why we use nulls to represent unknown values (see “Nulls” in Chapter 3).ĢVL is insufficient to represent knowledge gaps, so SQL uses three-value logic (3VL). 2VL assumes perfect knowledge, in which all propositions are known to be true or false. In two-value logic, the result of a logical expression is either true or false. If you’ve programmed in other languages (or studied propositional logic), you’re familiar with the two-value logic (2VL) system. Logical operators, or Boolean operators, are operators designed to work with truth values: true, false, and unknown. AND, OR, and a third operator, NOT, are logical operators. You can use the AND and OR operators to combine two or more conditions into a compound condition. You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. Learn More Buy Combining and Negating Conditions with AND, OR, and NOT SQL: Visual QuickStart Guide, 3rd Edition
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |