Results 1 to 3 of 3

Thread: Subqueries used in MySQL

  1. #1
    Join Date
    Sep 2010
    Posts
    48

    Subqueries used in MySQL

    The introduction of subqueries, which occurred in MySQL version 4.1, was probably the most anticipated innovation by users of this database. In the longer it has been pointed out that the lack of some features penalized greatly in comparison with other RDBMS MySQL, and the lack of subqueries was certainly among those which were noted. A subquery is simply a SELECT statement within another statement. Subqueries can also be nested in considerable depth. We have already seen that every logically SELECT returns a table consisting of rows and columns. In the case of the subquery must make a distinction: because they can return a single value (scalar), a single row, single column, or a regular table. The different types of subqueries can be placed in different parts of education.

    The Subquery as Scalar operand

    The simplest case is that the subquery returns a single value. You can use it anywhere you can use a column value. The most common use we find it as a comparison operator:
    Code:
    SELECT Column1 FROM t1 
    WHERE Column1 = (SELECT MAX (column2) FROM t2);
    This query extracts the values of Column1 in table t1 that are equal to the maximum column2 in table t2.

  2. #2
    Join Date
    Sep 2010
    Posts
    48

    Re: Subqueries used in MySQL

    Subqueries that return columns

    When a subquery returns a column, can be used to make comparisons across operators ANY, SOME, and ALL IN:
    Code:
    SELECT s1 FROM t1 WHERE s1> ANY (SELECT s1 FROM t2); 
    SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
    The first query does "select s1 from t1 values that are greater than at least one of t2 values on s1. The second one selects the values of s1 that is equal to at least one of t2 values on s1. "IN" stands for "= ANY". "SOME" is equivalent instead in all respects to "ANY".
    Code:
    SELECT s1 FROM t1 WHERE s1> ALL (SELECT s1 FROM t2);
    The meaning here is "select s1 from t1 values that are greater than all the values of s1 on t2. The clause "NOT IN" is equivalent to <> ALL".

    Subqueries that return rows

    When a subquery returns a single row, it can be used to make comparisons across manufacturers lines:
    Code:
    SELECT Column1, column2 FROM t1 
    WHERE (Column1, column2) IN 
    (SELECT Column1, column2 FROM t2);
    This query extracts the rows of t1 in which the values of Column1 and Column2 are repeated in a row in t2. The expression "(Column1, column2)" is, in fact, a row constructor, which could be expressed as "ROW (Column1, column2).

  3. #3
    Join Date
    Sep 2010
    Posts
    48

    Re: Subqueries used in MySQL

    Correlated subqueries

    The correlated subqueries are those that contain a reference to a table that is part of the outer query:
    Code:
    SELECT * FROM t1 WHERE Column1 = ANY 
    (SELECT Column1 FROM t2 WHERE t2. Column2 = t1. Column2);
    In this subquery, the WHERE clause contains a reference to the table t1, but is not named in the FROM clause of the subquery itself, as we find in the outer query's FROM. Queries of this nature require that the subquery is re-executed for each line extracted from the outer query, and therefore are not very efficient. Better to avoid them when possible: in fact, often a correlated subquery is transformed into a join. The correlated subqueries are sometimes used with the EXISTS and NOT EXISTS clauses, the EXISTS clause is true if the subquery returns at least one row, and is false otherwise. Obviously NOT EXISTS works in reverse.


    Subqueries in the FROM clause

    You can also use a subquery in the FROM clause, using the following syntax:
    Code:
    SELECT ... FROM (subquery) [AS] name ...
    Note that you must name the subquery, to be able to reference in other parts of the query. For example:
    Code:
    SELECT sq .* t2.c1 
    FROM (SELECT c1, c2, c3 FROM t1 WHERE c1> 5) AS sq 
    LEFT JOIN t2 ON sq.c1 = t2.c1;
    In this case the output of the subquery is called "sq" and the reference is used in both the SELECT is in the join condition.

Similar Threads

  1. How are subqueries different from Joins queries in SQL ?
    By Karumbu in forum Software Development
    Replies: 3
    Last Post: 25-01-2011, 04:58 AM
  2. Replies: 4
    Last Post: 13-01-2011, 01:08 AM
  3. Subqueries in oracle database
    By Landan in forum Software Development
    Replies: 4
    Last Post: 09-02-2010, 05:57 PM
  4. Replies: 3
    Last Post: 07-11-2009, 09:36 PM
  5. About mysql table subqueries
    By Benito in forum Software Development
    Replies: 2
    Last Post: 04-08-2009, 06:07 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,668,615.94323 seconds with 17 queries