Summary

Joins can be much more complicated than the simple two-table joins covered in Chapter 1, "Quickstart to MySQL." Inner joins ignore NULL values in a table being joined (or rows where there is no associated records), and outer joins include NULL data. Left outer joins return all data in the table specified first (on the left), including those without an associated record in the right table, while right outer joins return all data in the table specified on the right of the join. Full outer joins combine the features of a left and a right join, but MySQL does not yet support this.

Natural joins use the fact that common fields may be named the same, and simplify the syntax if this is the case.

The UNION command combines the results of more than one query into one.

Sub-selects are queries within queries. Often they perform more efficiently if they are rewritten as a join.

Deleting records one by one, as with the DELETE statement, is not efficient if you just want to remove all the records in a table. The TRUNCATE statement is a quicker way of doing this, though it doesn't return the number of records deleted, as DELETE does.

User variables allow you to store values for use in a later query. You need to take care when using them, however, that the user variable is set before it is required. In SELECT statements, the condition (the WHERE clause) is performed first, before the field list (immediately after the SELECT and where user variables are usually set).

MySQL can also be run in batch mode, with SQL statements stored in files for ease of editing and reuse. You can also redirect the output to a file, so for example, results of queries can be examined easily at a later stage.

All table types also allow table locking, where the entire table can be locked, as opposed to just the row as with transaction-safe tables.

In the next chapter, you'll sharpen your skills some more and look at how you can optimize the performance of our database. You'll explore creating indexes, writing queries more efficiently, and improving the server's performance.