The JOIN follows the basic SELECT syntax. The only addition is the JOIN clause. After the keyword
JOIN, the name of the table that shares a relationship with the table after the FROM keyword is used.
Following the table name are the JOIN criterion. This criterion is basically the definition of the
relationship between the two tables. It is also the same logic that you would use in your WHERE clause.
After the JOIN clause is the WHERE clause. The WHERE clause would add further limiting criteria to your
SELECT statement. If you wanted to see all the orders for a customer, you could use the following
syntax:
SELECT C.First_Name, C.Last_Name, O.Order_Date, O.Order_ID
FROM Customers as C
JOIN Orders as O ON C.Customer_ID = O.Customer_ID
WHERE Customer_ID = 12
The old way would look like the following:
SELECT C.First_Name, C.Last_Name, O.Order_Date, O.Order_ID
FROM Customers as C, Orders as O
WHERE C.Customer_ID = O.Customer_ID
AND C.Customer_ID=12
Again, whichever way you choose to use is entirely up to you. It is mostly a matter of preference. There
can be a small gain in performance using the ANSI-92, but sometimes, for the sake of readability and
clarity, the old way is preferred.
MySQL supports the following JOINs: CROSS JOIN, INNER JOIN, LEFT JOIN, and NATURAL LEFT
JOIN. Don't let the names or style confuse you. Just remember that you can implement a JOIN by
using logic in your WHERE clause. Both styles, the ANSI-92 and the old way, will be covered today. Use
what feels comfortable and makes the most sense to you.
CROSS JOIN
The CROSS JOIN is not used very much at all. In fact, most of the time it is done by mistake. A CROSS
JOIN returns all the records from all the tables mentioned in the JOIN. This is also referred to as a
Cartesian join]. These joins are very processor-intensive and should be avoided. The syntax for a
CROSS JOIN would look like the following:
The old way:
SELECT C.First_Name, C.Last_Name, O.Order_ID
FROM Customers as C, Orders as O
The ANSI-92 way:
SELECT C.First_Name, C.Last_Name, O.Order_ID
FROM Customers as C
CROSS JOIN Orders as O
The resultset that is returned from this type of join is huge. It basically returns all the data in all the
tables. There aren't too many uses for a join like this.
INNER JOIN
INNER JOINs are probably the most common of all joins.
An INNER JOIN simply means that all records that are unmatched are discarded. Only the matched
rows are displayed in the resultset. This is the default type of join, so the word INNER is optional. This
type of join is based on the criteria in the JOIN clause. Your first example was an example of an INNER
JOIN:
SELECT C.First_Name, C.Last_Name, O.Order_ID
FROM Customer as C
(INNER) JOIN Orders as O ON C.Customer_ID = O.Customer_ID
Again, the word INNER appears in parentheses because it is optional. The old way to create an INNER
JOIN would look like the following:
SELECT First_Name, Last_Name, O.Order_ID