Web Database Applications with PHP and MySQL, 2nd Edition

Advanced Querying

In Chapter 5, we covered most of the querying techniques you'll need to develop web database applications. In this section, we show you selected advanced techniques including shortcuts for joins, other join types, how to use aliases, using MySQL's new nested query support, working with user variables, and obtaining subtotals using WITH ROLLUP. This section concludes with a list of what we've omitted, and what MySQL doesn't yet include.

Advanced Join Types

This section introduces you to the INNER JOIN, LEFT JOIN, RIGHT JOIN, and UNION statements. The INNER JOIN statement is a shortcut that can save you some typing (and we use it throughout many examples in this chapter), LEFT JOIN and RIGHT JOIN add new functionality to find rows that don't have a match in another table, and UNION brings together the results from two separate queries.

Natural and inner joins

In the Chapter 5, we showed you how to perform a join between two or more tables. For example, to join the customer and orders tables, with the goal of displaying customers who've placed orders, you would type:

SELECT DISTINCT surname, firstname, customer.cust_id FROM customer, orders
  WHERE customer.cust_id = orders.cust_id;

The join condition in the WHERE clause limits the output to only those rows where there's a matching customer and order (and the DISTINCT clause presents each customer's details once).

We've referred to our example query as a natural join, but this isn't strictly correct. A natural join (which ...