MySQL SELECT - JOIN versus UNION, WHERE versus HAVING, GROUP etc.

OPTIONAL CLAUSES TO SELECT

In MySQL, the simplest of SELECT commands sends the whole contents of a table to the client. That's exactly what we want on some occasions, but falls far short of other more sophisticated requirements, which can be met using a variety of additional keywords and clauses.

SELECT CLAUSES - THE VARIOUS TYPES

- From v into
- Group v order v limit
- join v union
- where v having

FROM must be used to select the table(s) from which you wish to read your input(s) and INTO OUTFILE can be used to divert the output to a file.

Tables can be connected alongside each other using a JOIN, and the rows of several selects can be combined above and below each other using a UNION.

GROUP can be used collect together a series of rows into a single (summary) row in the output, and ORDER can be used to sort the rows. If you don't want a complete set of results, you can restrict the number of output rows using a LIMIT.

Individual input rows (but after any JOINs) can be selected by criteria specified in a WHERE clause, and combined rows (after any GROUPing) can be checked against criteria specified in a HAVING clause.

EXAMPLE SHOWING EACH CLAUSE IN PRACTICAL USE

Let's say we have tables of Estate Agents and Properties they have for sale, and we're planning a weekend trip to Wiltshire to look at some possible homes.

Using a FROM clause we select the tables that we're choosing the data from and we use a JOIN and ON sections to link them together. We'll actually use a RIGHT JOIN to ensure that we give all properties a fair crack of the whip, including those which aren't listed with an agent at all, even through they're in our table of properties.

Link - full explanation JOIN v LEFT JOIN v RIGHT JOIN
Our Criteria (before grouping) are specified using a WHERE clause, and we'll choose only properties under 480000 pounds, also excluding Westbury (before you write and ask - Westbury is a lovely place and I don't know why. Perhaps we don't want to be too close to a relative who already lives there).

Rather than list every possible property in a separate row, we'll GROUP them so that we just have one output row per agent.

Any additional criteria after grouping are specified with a a HAVING clause, and because our time in Wiltshire is limited, we're only going to look at agents with more than one appropriate property.

We list out the columns we need in our output directly after the SELECT command, and we use a UNION to combine two results sets - the first of which is just a set of headers. This allows us to label our results.

The output is to be to a text file called /tmp/weekend (using an INTO OUTFILE clause), and we'll make it a CSV file - comma separated variables - using a FIELDS subclause. This will allow the file to be imported to a spread sheet application on our laptop when we're on the road.

To help make our visit efficient, we'll use an ORDER clause to sort the possible agents by their base town, and in each town will start off with the one that offers the highest average price within out budget first - no point in looking at the cheap stuff when we have a good budget.

Finally, we'll use a LIMIT clause to give us just the first few results. Since we've already chosen to sort the results with the most appropriate first.

Here's the SQL SELECT command described:

SELECT 'Name of Estate Agency',
'Estate Agent\'s Town',
'Estate Agent\'s Phone',
'AID (key)',
'count of matches',
'average price',
'lowest price'

UNION

(SELECT agent, town, phone, aid, count(asking),
avg(asking) AS avga, min(asking)
INTO OUTFILE "/tmp/weekend"
FIELDS TERMINATED BY "," ENCLOSED BY '"'
FROM agents RIGHT JOIN sales ON aid = agid
WHERE asking < 480000 AND locate != "Westbury"
GROUP by aid
HAVING count(asking) > 1
ORDER BY town, avga DESC
LIMIT 4)

QUERY RESULTS

"Name of Estate Agency","Estate Agent's Town","Estate Agent's Phone",
>>>> "AID (key)","count of matches","average price","lowest price"
"","","","","2","370000.0000","275000"
"Kavanaghs","Melksham","01225 706860","10","3","219166.6667","195000"
"Jayson Kent","Melksham","01225 707798","4","3","175816.6667","127500"
"DK Residential","Trowbridge","01225 759123","3","2","343500.0000","222000"

Title line folded for display purposes
Note - the odd looking first data line is for properties for sale that are not listed as being with any agents - those were selected because we used a RIGHT JOIN and not a simple JOIN.

Incoming tables (for reference):

mysql> select * from agents;

---------------------------+-----------------+------------------+--------------+-----+
| agent | street | town | phone | aid |
---------------------------+-----------------+------------------+--------------+-----+
| Alder King | 63, Fore Street | Trowbridge | 01225 754301 | 1 |
| Connells | | Trowbridge | 01225 754391 | 2 |
| DK Residential | 9 Church Street | Trowbridge | 01225 759123 | 3 |
| Jayson Kent | 35 Market Place | Melksham | 01225 707798 | 4 |
| Halifax | | Trowbridge | 01225 752375 | 5 |
| Halifax | | Melksham | 01225 703773 | 6 |
| Greg Pullen | | Devizes | 01380 724040 | 7 |
| Town and Country Estates | 16 High Street | Westbury | 01373 824444 | 8 |
| Davies and Davies | | Bradford on Avon | 01225 867555 | 9 |
| Kavanaghs | 13 High Sreet | Melksham | 01225 706860 | 10 |
---------------------------+-----------------+------------------+--------------+-----+

mysql> select * from sales;

-------+------------+--------+-----------------------------------------------------+-----+
| agid | locate | asking | brief | sid |
+------+------------+--------+-----------------------------------------------------+-----+
| 10 | Semington | 225000 | Double fronted two story apartment | 1 |
| 10 | Melksham | 195000 | Attractive detached older style family home | 2 |
| 10 | Atworth | 237500 | Attractive four bedroom village home | 3 |
| 8 | Westbury | 152000 | Three bedroomed semi | 4 |
| 8 | Trowbridge | 205000 | Four bedroom detached in cul de sac | 5 |
| 6 | Melksham | 229950 | Four bedroomed semi in Aloeric school catchment | 6 |
| 5 | Trowbridge | 335000 | Detached family home with no onward chain | 7 |
| 1 | Trowbridge | 96950 | Well presented one bedroom flat | 17 |
| 5 | Hilperton | 279950 | Modern detached home | 8 |
| 4 | Melksham | 149950 | For all those non-gardeners | 9 |
| 1 | Trowbridge | 220000 | semidetached family home | 18 |
| 4 | Melksham | 127500 | Within a presigious retirement complex | 10 |
| 11 | Melksham | 465000 | Semidetached Spa house on town outskirts | 11 |
| 3 | Semington | 222000 | A pleasing detached modern residence | 12 |
| 3 | Hilperton | 465000 | An individual detached quality home | 13 |
| 2 | Westbury | 140000 | Three bedroom semidetached house | 14 |
| 2 | Trowbridge | 116000 | Larger than average first floor flat | 15 |
| 11 | Melksham | 275000 | Grade II Georgian townhouse requiring modernisation | 16 |
| 4 | Semington | 250000 | One bedroomed smarter home | 20 |
-------+------------+--------+-----------------------------------------------------+-----+