Using Subqueries In MySQL (part 2) - Cleaning Up
(Page 9 of 9 )
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!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 14 
