Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
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 |
-------+------------+--------+-----------------------------------------------------+-----+



See also MySQL training course

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

More MySQL commands
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [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)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [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)

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.


Visitor Ranking 5.0 (5=excellent, 1=poor)

edit your own (not yet published) comments

Average page ranking - 5.0

© WELL HOUSE CONSULTANTS LTD., 2019: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01225 708225 • FAX: 01225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-my ... p-etc.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard