Which of the following syntax is are desired when a subquery uses multiple columns of data?

While you cannot equate a single value to a set of values, you can check to see if a single value is found within that set of values. Use the IN clause for multiple-record, single-column subqueries. After the subquery returns results introduced by IN or NOT IN, the outer query uses them to return the final result.

[NOT] IN subqueries take the following form:

There is no limit to the number of parameters passed to the IN clause of the SELECT statement; for example:

=> SELECT * FROM tablename WHERE column IN (a, b, c, d, e, ...);

Vertica also supports queries where two or more outer expressions refer to different inner expressions:

=> SELECT * FROM A WHERE (A.x,A.x) IN (SELECT B.x, B.y FROM B);

Examples

The following query uses the VMart schema to illustrate the use of outer expressions referring to different inner expressions:

=> SELECT product_description, product_price FROM product_dimension WHERE (product_dimension.product_key, product_dimension.product_key) IN (SELECT store.store_orders_fact.order_number, store.store_orders_fact.quantity_ordered FROM store.store_orders_fact); product_description | product_price -----------------------------+--------------- Brand #72 box of candy | 326 Brand #71 vanilla ice cream | 270 (2 rows)

To find all products supplied by stores in MA, first create the inner query and run it to ensure that it works as desired. The following query returns all stores located in MA:

=> SELECT store_key FROM store.store_dimension WHERE store_state = 'MA'; store_key ----------- 13 31 (2 rows)

Then create the outer or main query that specifies all distinct products that were sold in stores located in MA. This statement combines the inner and outer queries using the IN predicate:

=> SELECT DISTINCT s.product_key, p.product_description FROM store.store_sales_fact s, public.product_dimension p WHERE s.product_key = p.product_key AND s.product_version = p.product_version AND s.store_key IN (SELECT store_key FROM store.store_dimension WHERE store_state = 'MA') ORDER BY s.product_key; product_key | product_description -------------+--------------------------------------- 1 | Brand #1 white bread 1 | Brand #4 vegetable soup 3 | Brand #9 wheelchair 5 | Brand #15 cheddar cheese 5 | Brand #19 bleach 7 | Brand #22 canned green beans 7 | Brand #23 canned tomatoes 8 | Brand #24 champagne 8 | Brand #25 chicken nuggets 11 | Brand #32 sausage ... ... (281 rows)

When using NOT IN, the subquery returns a list of zero or more values in the outer query where the comparison column does not match any of the values returned from the subquery. Using the previous example, NOT IN returns all the products that are not supplied from MA.

Notes

Vertica supports multicolumn NOT IN subqueries in which the columns are not marked NOT NULL. If one of the columns is found to contain a NULL value during query execution, Vertica returns a run-time error.

Similarly, IN subqueries nested within another expression are not supported if any of the column values are NULL. For example, if in the following statement column x from either table contained a NULL value, Vertica returns a run-time error:

=> SELECT * FROM t1 WHERE (x IN (SELECT x FROM t2)) IS FALSE; ERROR: NULL value found in a column used by a subquery

Overview

Single-Row Subquery

Multiple-Row Subquery

Multiple-Column Subquery

Overview

Subqueries can be used to answer queries such as "who has a salary more than Tom's". For such query, two queries have to be executed: the first query finds Tom's salary and the second finds those whose salary is greater than Tom's. Subquery is an approach provides the capability of embedding the first query into the other:  Oracle executes the subquery first, making the result of the sub query available to the main query and then executing the main query.

The syntax of subquery is

SELECT

FROM

WHERE expression operator

    ( SELECT

       FROM

      WHERE )

For example, the following statement answers the described query above.

    SQL> SELECT * FROM employee

         WHERE sal > (SELECT sal WHERE name='TOM');

Note that:

• a subquery must be enclosed in the parenthesis.
• a subquery must be put in the right hand of the comparison operator, and
• a subquery cannot contain a ORDER-BY clause.
• a query can contain more than one sub-queries.

In general, there are three types subqueries:

• single-row subquery, where the subquery returns only one row.
• multiple-row subquery, where the subquery returns multiple rows,.and
• multiple column subquery, where the subquery returns multiple columns.

Single-row subqueries can only be used with single-row comparison operators, and multiple-row subqueries can be used only with multiple-row operators.  They are to be described separately in the following.

---

Single-Row Subquery

The operators that can be used with single-row subqueires are =, >, >=, <, <=, and <>.

Group functions can be used in the subquery. For example, the following statement retrieve the details of the employee holding the highest salary.

SQL> SELECT * FROM employee

     WHERE sal = (SELECT MIN(sal) FROM employee);

Having-clause can also be used with the single-row subquery. For example, the following statement returns all departments in where the minimum salary is more than the minimum salary in the department 5.

SQL> SELECT dept_no, MIN(sal) FROM employee

     GROUP BY dept_no

  HAVING MIN(sal) > (

      SELECT MIN(sal)

        FROM employee

        WHERE dept_no = 5);

---

Multiple-Row Subquery

Note the following statement is illegal, because the operator = cannot be used with subquery returns multiple rows.

SQL> SELECT name, sal FROM employee

  WHERE sal > (

      SELECT MIN(sal) FROM employee GROUP BY dept_no);

Some operators that can be used with multipe-row subqueries are:

1. IN, equal to any member in the list,
2. ANY, compare values to each value returned by the subquery.

For example, the following statement find the employees whose salary is the same as the minimum salary of the employees in some department.

SQL>  SELECT name, sal FROM employee

  WHERE sal IN (

      SELECT MIN(sal)

        FROM employee

        GROUP BY dept_no);

For example, the following statement find the employees whose salary is more than the minimum salary of the employees in any department.

SQL> SELECT name, sal FROM employee

  WHERE sal > ANY (

      SELECT MIN(sal)

        FROM employee

        GROUP BY dept_no);

---

Multiple-Column Subquery

In multiple-column  subqueries, rows in the subquery results are evaluated in the main query in pair-wise comparison. That is, column-to-column comparison and row-to-row comparison.

For example, the following statement lists all items whose quantity and product id match to an item of order id 200.

SQL> SELECT order_id,  product_id, quantity

    FROM item

    WHERE (product_id, quantity) IN (

        SELECT  product_id, quantity FROM item WHERE order_it = 200)

    AND order_id = 200;

Note that you can put a subquery in the FROM clause in the main query.

For example, the following statement finds all employees in each department where their salary is above the average.

    SQL> SELECT a.name, a.sal, a.dept_no, b.salary_ave

        FROM employee a,

               (SELECT dept_no, AVE(sal) salary_ave

              FROM employee 

                GROUP BY dept_no)

        WHERE  a.dept_no = b.dept_no;

        AND a.sal > b.salary_ave;

---

When a subquery used multiple columns of data with one or more rows?

Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows. Contents: Using IN operator with a Multiple Row Subquery.

Which subquery use multiple columns of data?

Multiple-column subqueries enable you to combine duplicate WHERE conditions into a single WHERE clause. Column comparisons in a multiple-column subquery can be pairwise comparisons or nonpairwise comparisons. You can use a subquery to define a table to be operated on by a containing query.

Which of the following operators is used with a multiple row subquery?

The IN operator can be used with single-row, multiple-row, or multiple-column subqueries.

What is the syntax for a subquery?

You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc. A subquery is a query within another query. The outer query is known as the main query, and the inner query is known as a subquery.

Đọc tiếp

SQL subquery Correlated subquery

Reply 6 0 Chia sẻ