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

FROM Customer as C, Order as O

WHERE C.Customer_ID = O.Customer_ID

This is probably the most frequently used of all joins—the primary reason why it's the ANSI-92 default.

LEFT JOIN

A LEFT JOIN returns all rows from the left table in a join. For example, if you were to continue using

your Customers and Orders example, the following statement shows the syntax for a LEFT JOIN:

SELECT C.First_Name, C.Last_Name, O.Order_ID

FROM Customer as C

LEFT JOIN Order as O ON C.Customer_ID = O.Customer_ID

In this example, all the rows from the table on the left side of the equation will be returned, regardless of

whether they have a match with the table on the right side. If there is no match, a NULL value will be

returned.

In other database systems, the LEFT JOIN is implemented the "old way" using a symbol. The symbol

is an asterisk (*) equal sign (=) combination. The placement of the asterisk (left or right of the equal

sign) indicates the type of join. So, to implement a LEFT JOIN, the following would be the syntax:

SELECT C.First_Name, C.Last_Name, O.Order_ID

FROM Customers AS C, Orders as O

WHERE C.Customer_ID *= O.Orders_ID

The developers at TcX decided not to implement this feature in MySQL. They probably did it for several

reasons. First, joins of this type are rarely used. If you need to do a join like this, you can use the ANSI-

92 equivalent. Another reason why they probably chose not to implement this feature is that TcX works

on a very low budget. This is an extra feature that is rarely used—their time is better spent developing

the standards, not the extras.

The keyword USING can be used as a replacement for the ON keyword. The USING keyword allows you

to use a list of columns that appear in both tables and is equivalent to saying C.Customer_ID =

O.Customer_ID AND S.Shipper_ID = O.Shipper_ID. The syntax looks like the following:

SELECT C.First_Name, C.Last_Name, O.Orders_ID

FROM Customers AS C

LEFT JOIN Order as O USING Customer_ID

In this example, the USING keyword would be like using the ON keyword with C.Customer_ID =

O.Customer_ID following it. This is another shortcut to save time.

NATURAL LEFT JOIN

The NATURAL LEFT JOIN is the same as a regular LEFT JOIN except that it automatically uses all

the matching columns as part of the join. It is syntactically equivalent to a LEFT JOIN with a USING

clause that names all the identical columns of the two tables. The syntax looks like the following:

SELECT C.First_Name, C.Last_Name, O.Orders_ID

FROM Customers as C

NATURAL LEFT JOIN Orders as O

This would return all the rows from the Customers table, regardless of whether they had a matching

record in any of the same columns of the Orders table. The Orders table would return a NULL if it did

not have a match.

Again, this join is rarely used. The only way to accomplish this type of join is to use the given syntax.

Joins are fairly straightforward, But don't let the syntax fool you. Joins allow you to take advantage of

the relationships you defined earlier when you created the tables. Practice and experience will aid

greatly when creating joins.

Aggregate Functions

Aggregate functions are functions that perform a mathematical operation on a column. MySQL implements
the following aggregate functions: COUNT(), COUNT(DISTINCT), MAX(), MIN(), AVG(), SUM(), and
STD().