The Art of SQL

Stephane Faroult, Peter Robson

Mentioned 13

For all the buzz about trendy IT techniques, data processing is still at the core of our systems, especially now that enterprises all over the world are confronted with exploding volumes of data. Database performance has become a major headache, and most IT departments believe that developers should provide simple SQL code to solve immediate problems and let DBAs tune any "bad SQL" later. In The Art of SQL, author and SQL expert Stephane Faroult argues that this "safe approach" only leads to disaster. His insightful book, named after Art of War by Sun Tzu, contends that writing quick inefficient code is sweeping the dirt under the rug. SQL code may run for 5 to 10 years, surviving several major releases of the database management system and on several generations of hardware. The code must be fast and sound from the start, and that requires a firm understanding of SQL and relational theory. The Art of SQL offers best practices that teach experienced SQL users to focus on strategy rather than specifics. Faroult's approach takes a page from Sun Tzu's classic treatise by viewing database design as a military campaign. You need knowledge, skills, and talent. Talent can't be taught, but every strategist from Sun Tzu to modern-day generals believed that it can be nurtured through the experience of others. They passed on their experience acquired in the field through basic principles that served as guiding stars amid the sound and fury of battle. This is what Faroult does with SQL. Like a successful battle plan, good architectural choices are based on contingencies. What if the volume of this or that table increases unexpectedly? What if, following a merger, the number of users doubles? What if you want to keep several years of data online? Faroult's way of looking at SQL performance may be unconventional and unique, but he's deadly serious about writing good SQL and using SQL well. The Art of SQL is not a cookbook, listing problems and giving recipes. The aim is to get you-and your manager-to raise good questions.

More on Amazon.com

Mentioned in questions and answers.

What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area?

The idea of this question is to collect the shed load of resources that invariably exist, so that people can have a "one stop" knowledge shop of the good, peer approved resources.


General SQL

PostgreSQL (wiki) (PGsearch)

MySQL

Oracle

MS SQL Server

Sybase SQL Anywhere

JDBC

I'd start out by understanding how the database works at a fundamental level. How is data stored on disk, what does creating an index do, how does query plan optimization work, how are plans cached, when to cached plans expire.

If you can commit all that to memory, most of the advice about tuning seems obvious.

Here's a great book for MSSQL

SQL Server Internals

Xaprb is a must-read blog for MySQL DBAs. The author has written a book on high-performance MySQL

For the happy few working with Sybase SQL Anywhere I can only recommend Breck Carter's blog and his SQL Anywhere Studio 9 Developer's Guide

For Microsoft SQL, I'd recommend the books by Kalen Delaney (et al) called "Inside SQL Server". They offer a good insight into the internals of SQL Server, thus allowing readers to educate themselves on why particular statements might be faster than others.

Inside SQL Server 7.0
Inside SQL Server 2000
Inside Microsoft SQL Server 2005
Microsoft SQL Server 2008 Internals

There's also a book dedicated to performance tuning of SQL Server 2008 queries: SQL Server Performance Tuning Distilled

I also like the blogs by Paul Randal and Kimberly Tripp on SQLSkills.com. They are full of solid SQL advice:

Paul's blog
Kimberly's blog

Does anyone know where I can find a library of common but difficult (out of the ordinary) SQL script examples. I am talking about those examples you cannot find in the documentation but do need very often to accomplish tasks such as finding duplicates etc.

It would be a big time saver to have something like that handy.

EDIT: Thanks everyone, I think this is turning into a great quick reference. The more descriptive the more effective it would be, so please if you see your way open - please edit and add some descriptions of what one could find. Many thanks to those that have already done so!

Riffing off the Celko answer: SQL For Smarties. This has great in depth chapters that will augment the SQL Puzzles book. Also there is another Celko book I just learned of named Joe Celko's Trees and Hierarchies in SQL for Smarties.

How do SQL developers go about keeping up on current techniques and trends in the SQL world? Are there any blogs, books, articles, techniques, etc that are being used to keep up to date and in the know?

There are a lot of opportunities out their for OO, procedural, and functional programmers to take part in a variety of open source projects, but it seems to me that the FOSS avenue is a bit more closed for SQL developers.

Thoughts?

Find challenging questions that test your TRANSACT-SQL knowledge ... personally I enjoy Joe Celko's SQL Puzzles and Answers.

Joe Celko's SQL Puzzles and Answers

I improve by analyzing slow and complex queries and looking for ways to improve them. This can be done in SQL Server by analyzing the Query Plan tools and looking for bottlenecks. Also I find the Visual Quickstart Guide guide to be good for quick reference.

I skimmed the answers and apparently nobody has mentioned Stephane Faroult's work.

I strongly suggest you should consider "The Art of SQL" (http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945), I found it really interesting and - amazingly enough, even fun to read.

Joe Celko's SQL Puzzles and Answers and SQL for Smarties are the two best generic SQL books out there. Both are great sources to give you ideas for that tricky problem you used to think you needed a cursor or some client library to accomplish. For any truly interested SQL geek, these book are also pretty good for casual reading rather than as a mere desk reference. Two thumbs up.

I need to analyze 1 TB+ of web access logs, and in particular I need to analyze statistics relating to requested URLs and subsets of the URLs (child branches). If possible, I want the queries to be fast over small subsets of the data (e.g. 10 million requests).

For example, given an access log with the following URLs being requested:

/ocp/about_us.html
/ocp/security/ed-209/patches/urgent.html
/ocp/security/rc/
/ocp/food/
/weyland-yutani/products/

I want to do queries such as:

  • Count the number of requests for everything 'below' /ocp.
  • Same as above, but only count requests for child nodes under /ocp/security
  • Return the top 5 most frequently requested URLs.
  • Same as above, except group by an arbitrary depth,

e.g. For the last query above, depth 2 for the data would return:

2: /ocp/security/
1: /ocp/
1: /ocp/food/
1: /weyland-yutani/products/

I think the ideal approach would probably be to use a column DB and tokenize the URLs such that there is a column for each element in the URL. However, I would really like to find a way to do this with open source apps if possible. HBase is a possibility, but query performance seems too slow to be useful for real-time queries (also, I don't really want to be in the business of re-implementing SQL)

I'm aware there are commercial apps for doing this this type of analytics, but for various reasons I want to implement this myself.

The book, The Art of Sql, by Stephane Faroult has a very excellent chapter (7 - Dealing with Hierarchical Data) which explains and compares 3 methods for storing and querying trees using relational databases.

If you are doing a serious, industrial-strength implementation, studying the chapter will be time well spent.

Problem domain

I'm working on a rather big application, which uses a hierarchical data model. It takes images, extracts images' features and creates analysis objects on top of these. So the basic model is like Object-(1:N)-Image_features-(1:1)-Image. But the same set of images may be used to create multiple analysis objects (with different options).

Then an object and image can have a lot of other connected objects, like the analysis object can be refined with additional data or complex conclusions (solutions) can be based on the analysis object and other data.

Current solution

This is a sketch of the solution. Stacks represent sets of objects, arrows represent pointers (i.e. image features link to their images, but not vice versa). Some parts: images, image features, additional data, may be included in multiple analysis objects (because user wants to make analysis on different sets of object, combined differently).

Current solution simplified sketch

Images, features, additional data and analysis objects are stored in global storage (god-object). Solutions are stored inside analysis objects by means of composition (and contain solution features in turn).

All the entities (images, image features, analysis objects, solutions, additional data) are instances of corresponding classes (like IImage, ...). Almost all the parts are optional (i.e., we may want to discard images after we have a solution).

Current solution drawbacks

  1. Navigating this structure is painful, when you need connections like the dotted one in the sketch. If you have to display an image with a couple of solutions features on top, you first have to iterate through analysis objects to find which of them are based on this image, and then iterate through the solutions to display them.
  2. If to solve 1. you choose to explicitly store dotted links (i.e. image class will have pointers to solution features, which are related to it), you'll put very much effort maintaining consistency of these pointers and constantly updating the links when something changes.

My idea

I'd like to build a more extensible (2) and flexible (1) data model. The first idea was to use a relational model, separating objects and their relations. And why not use RDBMS here - sqlite seems an appropriate engine to me. So complex relations will be accessible by simple (left)JOIN's on the database: pseudocode "images JOIN images_to_image_features JOIN image_features JOIN image_features_to_objects JOIN objects JOIN solutions JOIN solution_features") and then fetching actual C++ objects for solution features from global storage by ID.

The question

So my primary question is

  • Is using RDBMS an appropriate solution for problems I described, or it's not worth it and there are better ways to organize information in my app?

If RDBMS is ok, I'd appreciate any advice on using RDBMS and relational approach to store C++ objects' relationships.

Just based on the diagram, I would suggest that an RDBMS solution would indeed work. It has been years since I was a developer on an RDMS (called RDM, of course!), but I was able to renew my knowledge and gain very many valuable insights into data structure and layout very similar to what you describe by reading the fabulous book "The Art of SQL" by Stephane Faroult. His book will go a long way to answer your questions.

I've included a link to it on Amazon, to ensure accuracy: http://www.amazon.com/The-Art-SQL-Stephane-Faroult/dp/0596008945

You will not go wrong by reading it, even if in the end it does not solve your problem fully, because the author does such a great job of breaking down a relation in clear terms and presenting elegant solutions. The book is not a manual for SQL, but an in-depth analysis of how to think about data and how it interrelates. Check it out!

Using an RDBMS to track the links between data can be an efficient way to store and think about the analysis you are seeking, and the links are "soft" -- that is, they go away when the hard objects they link are deleted. This ensures data integrity; and Mssr Fauroult can answer what to do to ensure that remains true.

Looks like my data warehouse project is moving to Teradata next year (from SQL Server 2005).

I'm looking for resources about best practices on Teradata - from limitations of its SQL dialect to idioms and conventions for getting queries to perform well - particularly if they highlight things which are significantly different from SQL Server 2005. Specifically tips similar to those found in The Art of SQL (which is more Oracle-focused).

My business processes are currently in T-SQL stored procedures and rely fairly heavily on SQL Server 2005 features like PIVOT, UNPIVOT, and Common Table Expressions to produce about 27m rows of output a month from a 4TB data warehouse.

I've been using MySQL for quite some time now. Most of that time I used it with PHP, for Joomla development. Up until now, I didn't pay very much attention to optimization, since I was usually asked to finish stuff ASAP.

Now, while I know that ASAP factor is a reality, I would like to improve my knowledge of relational DBs, together with good introspection to query and db optimization. I'm planning to start working with some rather large dbs, for which my usual approach will not be possible.

Any recommendations for some good books from the area?

Thx in advance.

Joe Celko's SQL for smarties, 4th ed.

The Art Of SQL

Refactoring SQL Applications

I would not recommend you to devote yourself to MySQL only. Instead, if you can, try to gain some experience with other DBMSs, where the advanced optimizers make your job easier.

I have a fairly complex sql that returns 2158 rows' id from a table with ~14M rows. I'm using CTEs for simplification.

The WHERE consists of two conditions. If i comment out one of them, the other runs in ~2 second. If i leave them both (separated by OR) the query runs ~100 seconds. The first condition alone needs 1-2 seconds and returns 19 rows, the second condition alone needs 0 seconds and returns 2139 rows.

What can be the reason?

This is the complete SQL:

WITH fpcRepairs AS
(
    SELECT FPC_Row = ROW_NUMBER()OVER(PARTITION BY t.SSN_Number ORDER BY t.Received_Date, t.Claim_Creation_Date, t.Repair_Completion_Date, t.Claim_Submitted_Date)
    ,   idData, Repair_Completion_Date, Received_Date, Work_Order, SSN_number, fiMaxActionCode, idModel,ModelName
    ,   SP=(SELECT TOP 1 Reused_Indicator FROM tabDataDetail td INNER JOIN tabSparePart sp ON td.fiSparePart=sp.idSparePart
            WHERE td.fiData=t.idData
            AND (td.Material_Quantity <> 0) 
            AND (sp.SparePartName = '1254-3751'))
    FROM   tabData AS t INNER JOIN
       modModel AS m ON t.fiModel = m.idModel 
    WHERE (m.ModelName = 'LT26i') 
    AND EXISTS(
        SELECT  NULL 
        FROM    tabDataDetail AS td 
        INNER JOIN tabSparePart AS sp ON td.fiSparePart = sp.idSparePart
        WHERE  (td.fiData = t.idData) 
        AND (td.Material_Quantity <> 0) 
        AND (sp.SparePartName = '1254-3751')
    ) 
), needToChange AS
(
    SELECT idData FROM tabData AS t INNER JOIN
       modModel AS m ON t.fiModel = m.idModel
    WHERE (m.ModelName = 'LT26i') 
    AND EXISTS(
        SELECT  NULL 
        FROM    tabDataDetail AS td 
        INNER JOIN tabSparePart AS sp ON td.fiSparePart = sp.idSparePart
        WHERE  (td.fiData = t.idData) 
        AND (td.Material_Quantity <> 0) 
        AND (sp.SparePartName IN ('1257-2741','1257-2742','1248-2338','1254-7035','1248-2345','1254-7042'))
    ) 
)
SELECT t.idData
FROM tabData AS t INNER JOIN modModel AS m ON t.fiModel = m.idModel
INNER JOIN needToChange ON t.idData = needToChange.idData  -- needs to change FpcAssy
LEFT OUTER JOIN fpcRepairs rep ON t.idData = rep.idData
WHERE   
rep.idData IS NOT NULL          -- FpcAssy replaced, check if reused was claimed correctly
AND rep.FPC_Row > 1             -- other FpcAssy repair before
AND (
    SELECT SP FROM fpcRepairs lastRep
    WHERE lastRep.SSN_Number = rep.SSN_Number
    AND lastRep.FPC_Row = rep.FPC_Row - 1
) = rep.SP                      -- same SP, must be rejected(reused+reused or new+new)
OR      
rep.idData IS NOT NULL          -- FpcAssy replaced, check if reused was claimed correctly
AND rep.FPC_Row = 1             -- no other FpcAssy repair before
AND rep.SP = 0                  -- not reused, must be rejected
order by t.idData 

Here's the execution plan:

Download: http://www.filedropper.com/exeplanfpc

Try to use UNION ALL of 2 queries separately instead of OR condition.

I've tried it many times and it really helped. I've read about this issue in Art Of SQL .

Read it, you can find many useful information about performance issues.

UPDATE:

Check related questions

UNION ALL vs OR condition in sql server query

http://www.sql-server-performance.com/2011/union-or-sql-server-queries/

Can UNION ALL be faster than JOINs or do my JOINs just suck?

Check Wes's answer

The usage of the OR is probably causing the query optimizer to no longer use an index in the second query.

I have a table that is similar to the following below:

       id |        cat |         one_above |        top_level | 
        0    'printers'          'hardware'        'computers'

I want to be able to write a query, without using unions, that will return me a result set that transposes this table's columns into rows. What this means, is that I want the result to be:

       id |          cat |
        0      'printers'
        0      'hardware'
        0     'computers'

Is this possible in MySQL? I can not drop down to the application layer and perform this because I'm feeding these into a search engine that will index based on the id. Various other DBMS have something like PIVOT and UNPIVOT. I would appreciate any insight to something that I'm missing.

Mahmoud

P.S.

I'm considering re-normalization of the database as a last option, since this won't be a trivial task.

Thanks!

I got this out of the book The Art of SQL, pages 284-286:

Let's say your table name is foo.

First, create a table called pivot:

CREATE Table pivot (
  count int
);

Insert into that tables as many rows as there are columns that you want to pivot in foo. Since you have three columns in foo that you want to pivot, create three rows in the pivot table:

insert into pivot values (1);
insert into pivot values (2);
insert into pivot values (3);

Now do a Cartesian join between foo and pivot, using a CASE to select the correct column based on the count:

SELECT foo.id, Case pivot.count
  When 1 Then cat
  When 2 Then one_above
  When 3 Then top_level
End Case
FROM foo JOIN pivot;

This should give you what you want.

I have tables "Crop", "Corn", "Soybean", and "Grain". One entry in Crop corresponds to a single entry in one of the other tables. The problem here is that Crop should be one-to-one with only one of the other tables, but not more than one. The Crop table is needed because it combines a lot of the common data from the other tables and makes querying the information much easier code side. From working on this I have a couple strategies with drawbacks...

A. Put three columns into Crop for the IDs of the other tables then populate the column "Corn" if it's a corn crop ect...

Drawbacks: Wasted columns, have to check all three columns whenever I want to see what crop it is

B. Combine Corn, Soybean, and Grain tables and add a single column for what type of crop it is.

Drawbacks: Each table has different columns, wasted and unnecessary columns in each row

Is it safe to say I'm stuck here? Or is there a strategy to handle cases like this? Thanks.

This is the "subtype" situation and is covered extensively in Stephane Faroult's the Art of SQL

The recommended solution involves using the same unique key (in this case, say CropID) across all tables, Crop, Corn, Soybean and Grain. The set of primary keys of the Crop table then becomes the union of primary keys of Corn, SoyBean and Grain. In addition, you define an attribute, say CropType, on the Crop table indicating the type of each Crop record. This way, common attributes stay on the Crop table and type-specific attributes go to type-specific tables with no redundancy.

Imagine a system that works with data, which structure changes over time. Say, for example, today your User object contains Name : String and Email : String, but tomorrow you need to add Age : Integer and Address which consists of Country, Zip code, etc. Then you may want to create new field User.Contacts and move Email and Address to that field, like refactoring. And it should be done in runtime, without coding and redeployment, because it will be done by customers or administrators, not developers.

What approaches and tools would you consider for storing such data? Will it be separate table for each class of objects and altering table each time structure is changed, or 1-to-many relationship between object and it's property values (like table StringProperties with fields ObjectID, PropertyID, StringValue); or one big table for all objects (with generic fields StringField1, NumericField2, etc)

How would you make indexing work?

Would you consider using less mainstream tools like CouchDB? Are there any other tools I should know about?

Are there any existing examples of application with similar idea - allowing users to define and augment their own data structures?

EDIT: I do not expect anyone to solve my entire design problem. Rough ideas or links to tools like CouchDB or Prevayler are more than welcome. Any article links are welcome too.

If the requirement is that you need the ability to create name-value pairs at random, then in some form or other, you'll end up with one or more tables of name-value pairs:

ID     USER_ID   PROPERTY_NAME         PROPERTY_VALUE
---------------------------------------------------------------
1      1         Name                  Chris
2      1         Occupation            Developer
3      2         Name                  Joe
4      2         Hair Color            Brown

... and so on. Of course, these name-value tables grow like crazy with time, so indexing and partitioning are important; to the extent you can classify property types into separate tables while still retaining the flexibility you need, you'll be able to keep the table size under relative control. I've worked on projects that used this approach, with table sizes stretching into the tens of millions of rows (on SQL Server & Oracle -- I can't vouch personally for others) without issue.

I'm no DBA, but in terms of indexing, my understanding is that you'll want one clustered index, to keep associated records in relative proximity on disk, and in my example, you'd probably want a non-clustered, non-unique index on USER_ID also, since you'd be querying directly against it, but beyond that, I don't have specific recommendations -- other than maybe to read Stephane Faroult's excellent book The Art of SQL, which offers way more insight than I ever could on this complex topic. Good luck!

I need to convert a lot of stuff in my profession - so I'm building a conversion tool for my phone with some of the conversions I use a lot.

Now, I want to be able to build this properly. So far, here's my code:

    <s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
    xmlns:s="library://ns.adobe.com/flex/spark" title="Length">

<fx:Script>
    <![CDATA[
        protected function button1_clickHandler(event:MouseEvent):void
        {
            var Result:String;
            var Finish:Number;
            var Start:Number = parseFloat(Input.text);
            Finish = Start * convert.selectedItem.data; 
            Result = String(Finish);
            answer.text = Result;

        }
    ]]>
</fx:Script>

<fx:Declarations>
    <!-- Place non-visual elements (e.g., services, value objects) here -->
</fx:Declarations>




<s:TextInput id="Input" x="20" y="46"/>
<s:SpinnerListContainer x="140" y="122" width="200" height="200">
    <s:SpinnerList id="convert" height="100%" width="100%" labelField="label" selectedIndex="1">
        <s:ArrayList>
            <fx:Object label="km to mi" data="0.62137119224"></fx:Object>
            <fx:Object label="km to yd" data="1093.6132983 "></fx:Object>
            <fx:Object label="km to ft" data="3280.839895"></fx:Object>
            <fx:Object label="km to in" data="39370.07874"></fx:Object>
            <fx:Object label="km to m" data="1000"></fx:Object>
            <fx:Object label="km to cm" data="100000"></fx:Object>
            <fx:Object label="km to mm" data="1000000"></fx:Object>

    </s:ArrayList>
            </s:SpinnerList>
        </s:SpinnerListContainer>
        <s:Label id="answer" x="66" y="533" width="348" text="Answer"/>
        <s:Button x="66" y="377" width="338"           click="button1_clickHandler(event)" label="Button"/>

    </View>

As you can see, I'm going to run into some problems with this:

1) Everything is hard-coded, and if I want to add, remove or change the elements in the array, it's going to be a bit of a pain.

2) I have a view that is essentially the same for my volume and weight conversions. With the same problem.

What I'd like to do, but I'm having some trouble understanding, is getting all that hard-coded stuff in one place and having the same view show it based on my previous view which is just a plain, hard-coded list.

I'm thinking of something like an xml sheet, and adding a category = "length" or category = "weight" element to the objects, so that I can show the category from the xml in the List, then when I click "length" it reads the label + data from this list. Is that a good solution? And how exactly do I get the selectedItem to remember which part of the xml list the view should be populated from?

Would it be better to have several xml files? But that still would mean I have to update a whole bunch of places when I need it.

Basically, I need assistance with:

So - Now the question is two-fold:

1) How to keep a connection to xml/db open across multiple views?

2) How to populate the end-view from information from the db?

Thanks for the advice and help.

I just finished an app about a month ago that's uses what I think would be the most "Flex"ible solution. (hehehehe)

(First Answer:) If you're into/familiar with (good) database design, you could design a SQLite db that allows you to add to & modify all the data you're working with.

(If not, I'd recommend either: http://www.amazon.com/The-Art-SQL-Stephane-Faroult/dp/0596008945/ref=sr_1_14?s=books&ie=UTF8&qid=1336262973&sr=1-14

or

http://www.amazon.com/SQL-Demystified-Andrew-Oppel/dp/0072262249/ref=sr_1_1?s=books&ie=UTF8&qid=1336263052&sr=1-1

...this post is going to take longer than I anticipated! hehehhehee ;P =D )

Basically what it'd be is: Tables for categories (e.g. Volume, Length, etc.) and another for specific name/value pairs ("km to mi" = 0.62137119224 [each in separate columns]) with a category id column to.

Then on your home page you have your init() create an DAO (Data Access Object [research if you don't know already]) for the categories table, then fetch the categories into an ArrayCollection and set it as the dataProvider on your category list (on the home view -- or wherever).

(Second Answer:) Have your change handler for the category list grab the selectedItem and pass it as the second param in navigator.pushView(). That will send the VO (Value Object -- another to research if you don't know it) to the new View as the "data" property.

In the "pushed view," use your creationComplete handler to "catch" (use) the data variable, which will contain the category's name and id. Create a new DAO for the values table and then use the data.id value to load all your values with that category id. Then set your new ArrayCollection as the dataProvider of your value list.

Then create another View for choosing values to edit in the same way. Except the final view in that "flow" would be a form with inputs for category, name, & value (with save & cancel buttons) that would get populated with the appropriate data too. (Note: use a category DAO to get the names of the categories so that the category names & ids are available if you change a category.

...Then it's just a matter of implementing insert & update methods on that View and the SQL & methods needed in each DAO.

You can use Lita (http://www.dehats.com/drupal/?q=node/58) build, design, pre-populate your database.

...I may come back with some nice example code/files (if I remember)

Good Luck Everyone! Todd

I made some examples for those who were reading and hoped I would...

//////////////////////////////////////  
//VO (Value Object)  
//Category.as  
//////////////////////////////////////  


package dao  
{  

[Bindable]//Makes all public properties bindable
public class Category
{   

    import mx.collections.ArrayCollection;

    public var id:int = -1;
    public var categoryName:String;
    private var categoryDao:CategoryDAO;

    public function Category() {}



    public function get exists():Boolean {
        return this.id > -1;
    }


    //"Super" convenient methods
    //Not really part of Value Objects / Value Object Pattern
    //May actually be a bad practice if you have many VO instances,
    //you have the potential for a DAO instance in each
    //when only one instance could be used.

    public function insert():void {
        if( !categoryDao ){ categoryDao = new CategoryDAO;}
        categoryDao.insert( this );
    }

    public function update():void {
        if( !categoryDao ){ categoryDao = new CategoryDAO;}
        categoryDao.update( this );
    }

    public function deleteRow():void {
        if( !categoryDao ){ categoryDao = new CategoryDAO;}
        categoryDao.deleteRow( this );
    }
}
}


//////////////////////////////////////  
//DAO (Data Access Object)  
//CatagoryDAO.as    
//////////////////////////////////////  


package dao
{
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.filesystem.File;

import mx.collections.ArrayCollection;

public class CategoryDAO
{
    public static var _sqlConnection:SQLConnection;
    public var failed:Boolean;
    public var errorMessage:*;

    public function CategoryDAO() {
    }

    public function getAll():ArrayCollection
    {
        var sql:String = "SELECT * FROM categories"                     
        + " ORDER BY categoryName ASC";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.execute();
        var result:Array = stmt.getResult().data;
        if( result ){
            var list:ArrayCollection = new ArrayCollection();
            for (var i:int=0; i < result.length; i++){
                list.addItem( buildVO( result[i] ) );   
            }
            return list;
        } else {
            return null;
        }
    }

    public function getByCategoryId(id:int):Category
    {
        var sql:String = "SELECT * FROM categories WHERE id=?";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[0] = id;
        stmt.execute();
        var result:Array = stmt.getResult().data;
        if( result && result.length == 1 ){
            return buildVO( result[0] );
        } else {
            return null;
        }
    }

    public function insert(category:Category):void
    {
        var sql:String = 
            "INSERT INTO categories ( categoryName )" + 
            " VALUES ( :name )";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[":name"] = category.categoryName;
        this.execute( stmt );
    }

    public function update(category:Category):void
    {
        var sql:String = 
            "UPDATE categories" +
            " SET categoryName = :name" +
            " WHERE id = :id";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[":name"] = category.categoryName;
        stmt.parameters[":id"] = category.id;
        this.execute( stmt );
    }

    public function deleteRow(category:Category):void {
        var sql:String = 
            "DELETE FROM categories" +
            " WHERE id = :id";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[":id"] = category.id;
        this.execute( stmt );
    }

    protected function execute(stmt:SQLStatement):void {
        try {
            stmt.execute();
        } catch(error:Error) {
            this.failed = true;
            this.errorMessage = error.message;
        } 
    }

    protected function buildVO(o:Object):Category
    {
        var category:Category = new Category();
        category.id = o.id;
        category.categoryName = o.categoryName;
        return category;
    }


    public function get sqlConnection():SQLConnection
    {
        if (_sqlConnection) return _sqlConnection;
        var file:File = 
               File.documentsDirectory.resolvePath(DbUtility.DB_FILE_NAME);
        var fileExists:Boolean = file.exists;
        _sqlConnection = new SQLConnection();
        _sqlConnection.open(file);
        return _sqlConnection;
    }
}
}



//////////////////////////////////////  
//CategoryView.mxml  
//////////////////////////////////////  

<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
    xmlns:s="library://ns.adobe.com/flex/spark"
    xmlns:dao="dao.*"
    opaqueBackground="#111111"
    title="All Categorys"
    creationComplete="init()">
<fx:Script>
    <![CDATA[
        import dao.DbUtility;
        import dao.DropPoint;
        import dao.Category;
        import dao.CategoryDAO;

        protected var dbVerifyUtil:DbUtility

        protected function init():void
        {
            dbVerifyUtil = new DbUtility;
            dbVerifyUtil.confirmDb();

            if( dbVerifyUtil.dbExists() ){
                var categorysDAO:CategoryDAO = new CategoryDAO;
                categoryList.dataProvider = categorysDAO.getAll();
            }

        }

        protected function categorySelected():void
        {
            navigator.pushView( CategoryListView, 
                    categoryList.selectedItem );
        }

        protected function newCategory():void
        {
            navigator.pushView( EditCategoryView );
        }

        protected function viewCategory():void
        {
            navigator.pushView( CategoryListView, 
                    categoryList.selectedItem );
        }

    ]]>
</fx:Script>


<s:List id="categoryList"
        left="10" right="10" top="10" bottom="85"
        change="viewCategory()"
        dataProvider="{data}"
        itemRenderer="irs.CategoryIR">
</s:List>


<s:Button label="Add Category"
          left="104" bottom="10" height="43"
          click="newCategory()"/>
<s:Label text="Touch a category to view or edit it."
         y="326" horizontalCenter="0"/>
</s:View>


//////////////////////////////////////  
//CategoryListView.mxml  
//////////////////////////////////////  

<?xml version="1.0" encoding="utf-8"?>
<s:View xmlns:fx="http://ns.adobe.com/mxml/2009" 
    xmlns:s="library://ns.adobe.com/flex/spark"
    xmlns:dao="dao.*"
    creationComplete="init()">

<fx:Script>
    <![CDATA[
        import dao.Value;//Value VO
        import dao.Category;//Category VO

        import mx.collections.ArrayCollection;

        import spark.events.IndexChangeEvent;

        private var category:Category;

        protected function init():void
        {
            var category:Category = data as Category;
            listValues.dataProvider =
                    valueDAO.getByCategoryId(
                            category.id );
        }

        protected function editValue():void
        {
            navigator.pushView( EditValueView,
                    listValues.selectedItem );
        }

        protected function editCategory():void
        {
            navigator.pushView( EditCategoryView, category );
        }

    ]]>
</fx:Script>

<s:viewMenuItems>
    <s:ViewMenuItem label="Edit Category"
                    click="editCategory()"
                    icon="@Embed('assets/edit.png')"/>
    <s:ViewMenuItem label="Add Location"
                    click="addLocation()"
                    icon="@Embed('assets/plus.png')"/>
</s:viewMenuItems>

<s:List id="listValues"
        left="10" right="10" top="10" bottom="60"
        labelFunction="labelValue"
        change="editValue()"
        itemRenderer="irs.ValueIR">
</s:List>
</s:View>

I want to know, When I use where clause following the directives join, What happen ?

In fact, something that I want to know is priority of execute. For instance, Consider the below code:

select * from tablename1 t1 inner join tablename2 t2 on t1.id=t2.id where id='10'

Then what happen in the above code first?

SQL will select all rows that their id are 10 and then runs join keyword ? Or join keyword runs in first and then where clause?

To answer your question Join runs first, but if you want to filter try this query

select * 
from (
  select *
  from tablename1
  where id='10') t1,
  (
  select *
  from tablename2 
  where id = '10'
) t2  

Update: As others suggest checking execution plan will help to understand how query performs, but still if you want to decide on your own, better to do it yourself.

Update 2: Thanks to @GiorgiNakeuri for his points. As mentioned in the comments after a change to inner queries Inner Join is not needed. A simple cartesian join is enough.

Read Art of SQL for more info on sql optimisations.

For more advanced knowledge try SQL Tuning