MySQL
  Home arrow MySQL arrow Page 3 - What’s New In MySQL 4.1 Part One: Overview and Subqueries
Dev Shed Forums  
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Smartphone Development  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Mobile Linux  
App Generation ROI  
IBM® developerWorks  
Forums Sitemap  
E-Commerce Hosting  
Linux Web Hosting  
Managed Hosting  
Small Business Hosting  
VPS Hosting  
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid  
Request Media Kit
Contact Us  
Site Map  
Privacy Policy  
Support  
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MYSQL

What’s New In MySQL 4.1 Part One: Overview and Subqueries
By: David Fells
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: starstarstarstarstar / 47
    2005-03-14


    Table of Contents:
  • What’s New In MySQL 4.1 Part One: Overview and Subqueries
  • Scalar and Correlated Subqueries
  • Derived Tables
  • Row Level Subquery

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      error-file:tidyout.log Del.ici.ous error-file:tidyout.log Digg
      error-file:tidyout.log Blink error-file:tidyout.log Simpy
      error-file:tidyout.log Google error-file:tidyout.log Spurl
      error-file:tidyout.log Y! MyWeb error-file:tidyout.log Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article

     
     
    ADVERTISEMENT


    What’s New In MySQL 4.1 Part One: Overview and Subqueries - Derived Tables
    ( Page 3 of 4 )

    The next type of subquery uses what is referred to as a derived table, appearing in the list above as “a table to select from.” In this usage, the subquery expression appears as a part of the FROM clause or to test membership. Subqueries used to test membership will be used with keywords like ANY, EXISTS, and NOT EXISTS in the outer query and may or may not be replaceable by a join. On the flip side are tables to select from, which are easily replaced with joins in nearly any situation. The tables created by these subqueries are called derived tables, which are essentially unnamed, non-persistent views.

    Testing membership is one of the core features that any implementation of SQL should provide. SQL is a set oriented language and memberhip tests are fundamental in set theory. While MySQL has provided the UNION operation for as long as I can remember, the EXISTS, NOT EXISTS, and ANY operations are new and much needed. Subqueries that test membership can also be correlated and are especially useful when performing complex reporting operations. In the following example, we will select values from one table where they do not exist in another.

    SELECT 
        column1
    FROM 
        table 1
    WHERE 
        NOT EXISTS
            (
             SELECT
                  column1 
             FROM
                  table2 
            )

    In this example, the inner query is executed first, selecting column1 from table2. The result of that subquery is used in the NOT EXISTS clause which, logically, is the same as using NOT IN and declaring a set of values by hand. In fact, the EXISTS and IN statements can be used interchangeably in this context.

    An example of a derived table in the FROM clause is shown below.


    SELECT
         column1 
    FROM
         (
          SELECT
               column1
          FROM
               table2
         )

    The process here is similar to that of a correlated subquery – the engine first processes the inner query, creating a derived table. That derived table is then used in the outer query as the target for record selection. These queries can also be correlated and are typically more useful in that context, but do have uses on their own.



     
     
    >>> More MySQL Articles          >>> More By David Fells
     

       

    MYSQL ARTICLES

    - MySQL Security Tips
    - Designing a MySQL Database: Tips and Techniq...
    - The Three Most Important MySQL Queries
    - Null and Empty Strings
    - MySQL Server Tuning Tips and Tricks
    - MySQL Query Optimizations and Schema Design
    - MySQL Benchmarking Tools and Utilities
    - MySQL Benchmarking Concepts and Strategies
    - Take Some Load off MySQL with MemCached
    - MySQL Table Prefix Changer Tool in PHP
    - Using the SIGNAL Statement for Error Handling
    - Error Handling Examples
    - Error Handling
    - Completing a Search Engine with MySQL and PH...
    - Paginating Result Sets for a Search Engine B...





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 3 Hosted by Hostway
    Stay green...Green IT