Home arrow MySQL arrow Page 9 - Using Subqueries In MySQL (part 2)

Cleaning Up - MySQL

In this concluding segment of our MySQL subquery tutorial, find out how to do more with subqueries, including check for matching values with the IN operator, check for valid result sets with the EXISTS operator, derive new "virtual" tables for use in the FROM clause of outer queries, and UPDATE and DELETE records selectively.

TABLE OF CONTENTS:
  1. Using Subqueries In MySQL (part 2)
  2. Total Recall
  3. In And Out
  4. A Solitary Existence
  5. Turning The Tables
  6. Show Me The Money
  7. Adjusting For Inflation
  8. A New Broom
  9. Cleaning Up
By: RK Harigopal, (c) Melonfire
Rating: starstarstarstarstar / 16
July 31, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

And that's about all we have time for. In this concluding segment of my MySQL subqueries tutorial, I explored some of the other ways in which subqueries can be used. These included membership tests with the IN operator and Boolean tests with the EXISTS operator; using subqueries to derive new tables which can then be used in the FROM clause of outer SELECT queries; and using subqueries with other SQL data manipulation commands like UPDATE and DELETE. In the context of the last item, I also focused on one of the more common things newbies trip up on - trying to write to a table in the outer query while it is being read by the inner one - and demonstrated an alternative approach to handling such a scenario.

As you have seen over the preceding pages, subqueries are a powerful tool, and open up all kinds of possibilities for the SQL developer. My intent, using a simple accounting system and some common (and no-so-common) tasks, was to demonstrate how subqueries can substantially simplify your life when working with complex MySQL databases, and why they're generally considered a Good Thing.

Remember, however, that power such as this comes at a price. Incorrectly-written subqueries can result in massive load increases on your RDBMS, and can reduce performance drastically, negating the overall gains provided by this feature. For this reason, I strongly advise you to always explore alternative methods of obtaining the data you need, including joins, unions and other SQL constructs (the first part of this tutorial demonstrates some of these constructs), so as to ensure optimal performance of your application and minimal resource wastage on the RDBMS.

Now go practice.

Note: Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!



 
 
>>> More MySQL Articles          >>> More By RK Harigopal, (c) Melonfire
 

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: