SQL ANY

3 Key excerpts on "SQL ANY"

• 

  eBook - ePub

  OCA: Oracle Database 12c Administrator Certified Associate Study Guide

  Exams 1Z0-061 and 1Z0-062

  • Biju Thomas(Author)
  • 2014(Publication Date)
  • Sybex

    (Publisher)

  ANY operator:

  SELECT first_name || ' ' || last_name "Name", department_id FROM employees WHERE department_id <= ANY (10, 15, 20, 25); Name DEPARTMENT_ID ------------------------------------------- ------------- Jennifer Whalen 10 Michael Hartstein 20 Pat Fay 20

  Oracle in fact expands the ANY condition to department_id <= 10 OR department_id <= 15 OR department_id <= 20 OR department_id <= 25 . The behavior of ANY with each comparison operator is

  1. X = ANY ( list ) : Evaluates to TRUE if value of X matches at least one value in the list.
  2. X != ANY ( list ) : Evaluates to TRUE if value of X does not match one or more value in the list.
  3. X > ANY ( list ) : Evaluates to TRUE if value of X is higher than the smallest value in the list.
  4. X < ANY ( list ) : Evaluates to TRUE if value of X is lower than the biggest value in the list.
  5. X >= ANY ( list ) : Evaluates to TRUE if value of X is higher than or equal to the smallest value in the list.
  6. X <= ANY ( list ) : Evaluates to TRUE if value of X is lower than or equal to the biggest value in the list.

  ALL

  You can use the ALL operator to compare a value to every value in a list or subquery. The ALL operator must always be preceded by one of the following comparison operators: = , != , < , > , <= , or >= . The following shows an example of using the ALL

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn SQL Database Programming

  Query and manipulate databases from popular relational database servers using SQL

  • Josephine Bush(Author)
  • 2020(Publication Date)
  • Packt Publishing

    (Publisher)

  It's important to remember that when using comparison operations such as =, >, <, >=, <=, or <>, your subquery can only return one row or you will receive an error: Error Code: 1242. Subquery returns more than 1 row.

  • Using ANY: This will return results in the outer query where the results of the outer query satisfy any of the results of the inner query. The ANY operator allows you to use a subquery that returns zero or more rows.

  You can also use comparison operators such as >= (greater than or equal to) with ANY and a non-correlated subquery in the WHERE clause, as shown in the following query: USE lahmansbaseballdb; SELECT playerid, yearid, salaryFROM salariesWHERE salary >= ANY (SELECT AVG(salary) FROM salaries GROUP BY teamid)ORDER BY playerid, yearid; The preceding query returns the results shown in the following screenshot: The preceding query will return the playerid, yearid, and salary columns if the salary of the player is greater than or equal to ANY of the average salaries for each teamid.

  • Using ALL: T
    his will return results in the outer query where the results of the outer query satisfy all of the results of the inner query. The ALL operator allows you to use a subquery that returns zero or more rows.

  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