Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

records that match your WHERE clause criteria would be decremented by up to 100. This would

continue until there are no records that match your criteria. By using this technique, you will be able to

perform your database cleanup without sacrificing performance.

Another point to consider when deleting records is that when a record is deleted, it does not just

disappear. It becomes part of an internal linked list. This means that even though the record is deleted,

it is still taking up space on disk. To remove the remnants of these deleted records, you will have to

perform an OPTIMIZE table query or use the myisamchk utility. Both of these tools will clean up the

space taken by these dead records.

Working with Joins

Joins are an integral part of a relational database. Joins allow the database user to take advantage of the
relationships that were developed in the design phase of the database. A join is the term used to describe
the act when two or more tables are "joined" together to retrieve needed data based on the relationships that
are shared between them. For example, in your Meet_A_Geek database, you have a relationship between
the Customers and Orders tables. To select data out of the Customers table based on some criterion that
is contained in the Orders table would require you to use a join of some kind. For example, to retrieve all
the customers who have placed an order, you could use the following syntax:

SELECT First_Name, Last_Name

FROM Customer AS C, Orders AS O

WHERE C.Customer_ID = O. Customer_ID

This would return only the records of customers who have placed orders, or where records exist in both

tables. If a customer has placed multiple orders, you would see all his or her orders.

ANSI-92 Compliance

In 1992, the American National Standards Institute developed a set of standards for SQL. These standards
are called ANSI-92. MySQL takes great pride in being truly ANSI-92–compliant. ANSI-92 standardizes the
ways to create joins. Instead of putting the joining logic in the WHERE clause, it allows you to put it in the
FROM clause. There are two schools of thought: those who have embraced the new way—the ANSI-92
way—and those who continue to use the WHERE clause for joins. The old way is easy to read and makes a
lot of sense. Plus, the majority of DBAs and programmers grew up learning it the old way. The new way does
offer some advantages. First, it is the ANSI standard. All new databases must use the ANSI-92 way of doing
things if they want to be compliant. So you can be sure that the new way will be around in the future. Also,
the new way is a little better when it comes to performance. Instead of MySQL parsing the WHERE clause and
then developing its query plan, the join is stated well before the WHERE clause is parsed, making the queries
a little better when it comes to performance.

The ANSI-92 way of implementing a join is a little different syntactically than the old way, but the

concept is the same. You want to combine the information in two tables based on a relationship into one

resultset. An ANSI-92 JOIN used to accomplish the same resultset as the previous example would look

like the following:

SELECT First_Name, Last_Name

FROM Customers as C

JOIN Orders as O ON C.Customer_ID = O.Customer_ID

The following is the basic format of the ANSI-92 JOIN:

SELECT Column List

FROM table name

JOIN table name ON join criteria

WHERE condition criteria