MySQL - the order of clauses and the order of actions
Archive - Originally posted on "The Horse's Mouth" - 2007-06-01 17:45:49 - Graham EllisYou 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 |
+-------+------------+------------+