Home arrow MySQL arrow Page 3 - What’s New In MySQL 4.1 Part One: Overview and Subqueries

Derived Tables - MySQL

The current release of MySQL, version 4.1.10, offers significant improvements over version 4. While it still has some room for improvement, its new features and capabilities should silence the critics who have up until now regarded it as little more than a toy. In this article, the first of two parts, David Fells covers scalar and correlated queries, derived tables, and row level subqueries.

TABLE OF CONTENTS:
  1. What’s New In MySQL 4.1 Part One: Overview and Subqueries
  2. Scalar and Correlated Subqueries
  3. Derived Tables
  4. Row Level Subquery
By: David Fells
Rating: starstarstarstarstar / 47
March 14, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: