You specify your select command in the following order:
1. The columns you want to choose (SELECT)
2. Where you want to send the results (INTO)
3. The tables that contain the data (FROM)
4. How you want to connect those tables (JOIN, ON)
5. Which individual rows you want to choose (WHERE)
6. Bunching of individual rows together (GROUP)
7. Which bunches you want to choose (HAVING)
8. How to sort the records (ORDER)
9. How many output records you want (LIMIT)

The order that MySQL performs these tasks is as follows:
1. Joining the tables
2. Selecting individual records
3. Sorting the records
4. Bunching
5. Selecting bunches
6. How many output records you want
7. Choosing the columns to display

Here's an example that shows some of the less common clauses in use:

drop table if exists calltimes;
drop table if exists townnames;
create table calltimes (
  cid int primary key not null auto_increment,
  pcode text,
  calls text);
create table townnames (
  tid int primary key not null auto_increment,
  pcode text,
  fullname text);
insert into calltimes values
  (1, "MKM", "06:44"),
  (2, "MKM", "07:17"),
  (3, "MKM", "19:08"),
  (4, "MKM", "19:50"),
  (7, "CPM", "16:25"),
  (5, "CPM", "16:55"),
  (6, "CPM", "17:25"),
  (8, "TRO", "23:42"),
  (9, "PNG", "15:10");
insert into townnames values
  (1, "MKM", "Melksham"),
  (2, "CPM", "Chippenham"),
  (3, "BTH", "Bath");
select calltimes.pcode, townnames.fullname, count(cid)
  from calltimes left join townnames
  on calltimes.pcode = townnames.pcode
  group by calltimes.pcode
  order by fullname;

And the results:

+-------+------------+------------+

| pcode | fullname   | count(cid) |

+-------+------------+------------+

| TRO   | NULL       |          1 |

| PNG   | NULL       |          1 |

| CPM   | Chippenham |          3 |

| MKM   | Melksham   |          4 |

+-------+------------+------------+

(written 2007-06-01 17:45:49)

 S157 - More MySQL Commands
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [673] Helicopter views and tartans - (2006-04-06)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [515] MySQL - an FAQ - (2005-12-03)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

Back to What brought YOU to our web site?

Previous and next or Horse's mouth home

Forward to New Serieses for the summer on TV

What are factory and singleton classes?
Five of the best - pictures from London
An update on Perl - where is it going?
New Serieses for the summer on TV
MySQL - the order of clauses and the order of actions
What brought YOU to our web site?
A lot has happened in a year
PHP header() function - uses and new restrictions
Where did the Bank Holiday go?
Meet other local businesses in Melksham