SQL IN

3 Key excerpts on "SQL IN"

• 

  eBook - PDF

  SQL

  Practical Guide for Developers

  • Michael J. Donahoo, Gregory D. Speegle(Authors)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  In most cases, the choice of syntax should not change the performance of the query; however, in a few cases, one variant can be much more efficient than another. This will depend on your database and your DBMS. 38 Chapter 2: Retrieval: Basic SELECTion ■ 2.9 Selecting a Set of Values Using IN The IN operator determines if a value is contained in a list of values. Query 2.18 Find the vendor representatives with last names of Corn or Sherbert SELECT vendorid, repfname, replname FROM vendors WHERE replname IN ('Corn', 'Sherbert'); vendorid repfname replname VGRUS Candy Corn FLVCR Sherman Sherbert [2 row(s)] The list of values must be comma-delimited and enclosed within parentheses. To evaluate WHERE x IN (y1, : : : , yn) , SQL evaluates x = yi for each yi in the list. If at least one x = yi evaluates to true, the condition evaluates to true. As you probably already suspect, you can use NOT IN to find all of the values not contained in a list. Query 2.19 Find the ingredient ID, name, and unit of items not sold in pieces or strips SELECT ingredientid, name, unit FROM ingredients WHERE unit NOT IN ('piece', 'strip'); ingredientid name unit CHESE Cheese scoop LETUS Lettuce bowl PICKL Pickle slice SCTDR Secret Dressing ounce TOMTO Tomato slice WATER Water glass SODA Soda glass ORNG Orange slice [8 row(s)] 2.10 IS NULL: Exploring the Unknown How do we find all of the vendors who were not referred by any other vendor? We need all of the rows from vendors with a referredby value of NULL, but beware— NULL ■ 2.10 IS NULL: Exploring the Unknown 39 is a strange animal. SQL INterprets NULL as unknown. If we compare something that is unknown to any value, even unknown, the result is unknown. This makes sense if you think about it. Consider a new vendor representative with first name Bob and an unknown last name. If somebody asked you if Bob’s last name was Smith, how could you most accurately answer? That’s right—“I don’t know.” The database equivalent is unknown.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Concepts of Database Management

  • Joy Starks, Philip Pratt, Mary Last, , Joy Starks, Philip Pratt, Mary Last(Authors)
  • 2018(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  For example, T?m represents the letter T followed by any single character, followed by the letter m . When used in a WHERE clause, it retrieves records that include the words Tim , Tom , or T3m , for example. Other versions of SQL use an underscore ( _ ) instead of the question mark to represent any individual character. Note: In a large database, you should use wildcards only when absolutely necessary. Searches involving wildcards can be extremely slow to process. Another operator, IN, provides a concise way of phrasing certain lists in a condition. Y O U R T U R N 3-14 List the number, name, street, and credit limit for every client with a credit limit of $5,000, $7,500, or $10,000. In this query, you can use the SQL IN operator to determine whether a credit limit is $5,000, $7,500, or $10,000 as shown in Figure 3-29. You could have obtained the same result by using the condition WHERE CreditLimit = 5000 OR CreditLimit = 7500 OR CreditLimit = 10000. The IN approach is a bit simpler, how-ever — the IN clause contains the collection of values 5000, 7500, and 10000, enclosed in parentheses and separated by commas. Recall that numeric data does not need quotation marks. The condition is true for those rows on which the value in the CreditLimit column is in this collection of values. 96 Chapter 3 List of values IN operator FIGURE 3-29 SQL query with an IN operator The query results appear in Figure 3-30. Only clients with credit limits of $5,000, $7,500, or $10,000 are listed FIGURE 3-30 Query results 97 The Relational Model 2: SQL S O R T I N G Recall that the order of rows in a table is considered to be immaterial. From a practical standpoint, this means that when you query a relational database, there are no guarantees concerning the order in which the results will be displayed. The results might appear in the order in which the data was originally entered, but even this is not certain.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  sequential unique index. Consider a table of employees keyed by a unique employee identification number. Updates are done with the employee ID number, of course, but very few queries use it for ordering reports. Updating individual rows in a table will actually be about as fast with a sequential or a nonsequential index. Both tree structures will be the same, except for the final physical position to which they point.

  However, it might be that the most important corporate unit for reporting purposes is the department, not the employee. A sequential index on the employee ID number would sort the table in employee ID order. There is no inherent meaning in that ordering; in fact, I would be more likely to sort a list of employees by their last names than by their ID numbers.

  However, a sequential index on the (nonunique) department code would sort the table in department order and put employees in the same department on the same physical page of storage. The result would be that fewer pages would be read to answer queries.

  Modern SQL products can use multiple indexes for AND and OR search criteria. If the index has all the columns needed for a query, it is possible that the SQL engine will never read the base tables.

  39.5 Watch the IN Predicate

  The IN predicate is really shorthand for a series of OR -ed equality tests. There are two forms: either an explicit list of values is given or a subquery is used to make such a list of values.

  The database engine has no statistics about the relative frequency of the values in a list of constants, so it will assume that the list is in the order in which they are to be used. People like to order lists alphabetically or by magnitude, but it would be better to order the list from most frequently occurring values to least frequent. It is also pointless to have duplicate values in the constant list, since the predicate will return TRUE if it matches the first duplicate it finds and will never get to the second occurrence. Likewise, if the predicate is FALSE for that value, the program wastes computer time traversing a needlessly long list.

  Many SQL engines perform an IN

  Sign up to read

  Learn more about book