SQL IN

3 Key excerpts on "SQL IN"

• 

  eBook - ePub

  Database Management Systems

  • Sotirios Zygiaris(Author)
  • 2018(Publication Date)
  • Emerald Publishing Limited

    (Publisher)

  In forming the criteria for the row selector, the WHERE clause with all conditional and logical operators is used. The logical and conditional operators in the WHERE clause return the value TRUE for each row that satisfies the criteria set. The OR operator must select rows from a predefined set of values and not from a range of possible values. In this case, the IN operator should be used instead of many repeating OR operators.

  Searching text with wildcards requires the use of the LIKE operator, rather than the (=) equal operator. This usually occurs when some repeating patterns of characters exist inside text data. Using arithmetic operations in numeric data types is a frequent operation. Any arithmetic operation (+−/*) may be applied to an arithmetic data type. An ORDER BY clause is always placed at the end of the query and rearranges the rows of a table accordingly in ascending or descending order.

  Aggregate functions (COUNT, SUM, AVG, MAX, and MIN) perform basic summarized operations in a database column. All five aggregate functions do not extract another table but a single value as either count, sum, average, maximum value, or minimum value. The aggregate functions are applied to a specific attribute in the table. In a single query, many different aggregate functions may be used.

  Key Terms

  Data Selection	Data Extraction	SELECT
  WHERE	IN Operator	ORDER BY
  Ascending Order	Descending Order	Aggregate Functions
  COUNT	SUM	AVG
  MIN	MAX	LIKE

  Review Questions

  (1) Using Desk Research, identify one case of complex query in the business world. Make a summary of 10 lines and include a video source in your answer.

  (2) Explain how SQL queries select and extract data from a table. Use an example.

  Problems and Exercises

  1. For one of the four cases solved in exercise 3.1 design the following queries:
     • a query that lists all contents of a table of your choice;
     • a query that selects only certain columns;
     • a query using the WHERE clause;
     • a query that is using AND/OR operators;
     • a query that is using the IN operator;
     • a query that is searching text with wildcards;
     • a query that is using search with DATE; and
     • queries that exercise aggregate functions.
  2. In the sales forecasting case, the following business requirements were set. A-Oil & Chemical is a chemical company that plans to create a database to forecast sales.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Relational Database Design and Implementation

  • Jan L. Harrington(Author)
  • 2016(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  sale where the sale ID is in the set of values retrieved by the subquery.

  The use of the IN operator is actually exactly the same as the use you read about in Chapter 16 . The only difference is that, rather than placing the set of values in parentheses as literals, the set is generated by a SELECT.

  When processing this query, the DBMS never joins the two tables. It performs the inner SELECT first and then uses the result table from that query when processing the outer SELECT. In the case in which the two tables are very large, this can significantly speed up processing the query.

  Note: You can also use NOT IN with subqueries. This is a very powerful syntax that you will read about in Chapter 18 .

  Using the ANY Operator

  Like IN, the ANY operator searches a set of values. In its simplest form, ANY is equivalent to IN:

  This syntax tells the DBMS to retrieve rows from sale , where the sale ID is “equal to any” of those retrieved by the SELECT in the subquery.

  What sets ANY apart from IN is that the = can be replaced with any other relationship operator (for example, < and >). For example, you could use it to create a query that asked for all customers who had purchased a book with a price greater than the average cost of a book. Because queries of this type require the use of SQL summary functions, we will leave their discussion until Chapter 19 .

  Nesting Subqueries

  The SELECT that you use as a subquery can have a subquery. In fact, if you want to rewrite a query that joins more than two tables, you will need to nest subqueries in this way. As an example, consider the following query that you saw earlier in this chapter:

  It can be rewritten as

  Note that each subquery is surrounded completely by parentheses. The end of the query therefore contains two closing parentheses next to each other. The rightmost) closes the outer subquery; the) to its left closes the inner subquery.

  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