Sub queries

A new thing in MySql 5 is the possibility to use something called "sql subqueries". It can be a very powerful new tool if you have not already tried it.

Subqueries can make it possible to do more advanced queries againts the database than usual. This way you don't have to do that much logic in your programming language (PHP etc).
It might be easier if I give you an example. Try to find all employees that are older than the average age on the company.

The old way:

Without subqueries you would do something like this:

1. "SELECT AVG(age) as averageAge FROM staff"
2. Store the value in a PHP variable ($averageAge)
3. Run a new query with this value:
   "SELECT firstName FROM staff WHERE age > $averageAge"
   

Not to hard, BUT with MySql you can do this so much easier and quicker :-)
And who don't want to do it easier???

The new way with subqueries:

SELECT firstName
FROM staff
WHERE age > (SELECT AVG(age) FROM staff)

This will do the exact same thing actually, only more compact and smarter :-)

First, note that we cannot write "WHERE age > AVG(age)". This is because we cannot use functions in the WHERE clause.
Instead we use a subquery to find the average age and then use that value to compare in the WHERE clause.

This was just a short example of what you can do with subqueries.
Think of sql subqueries as a new tool to use. You no longer always have to do things in three steps by going through PHP and then back to MySql again.