If you're travelling from Chippenham to London by train, you may want to grab a quick coffee at the station before you set off - and where better than Steamers? And if you were traveling to Plymouth, Bristol, Southampton, Llandrindod Wells or Cheltenham, you would use Steamers too.

Let's assume I'm keeping a database of journeys and where to get a coffee before I start the journey ... the data above is the sort of thing that I'll need to store. And I'll only want to store the details for Steamers ONCE no matter how many places I may travel to from Chippenham - to store the data multiple times would be (a) wasteful of space, (b) require a lot of effort in setting things up, (c) be a lot of work to update when things changed and (d) be prone to small errors which will be hard to spot as they will only come up on very occasional queries.

So ...
• I store the data for each journey ONCE in a table of journeys
• I store the data for each eating place ONCE in a table of eating places
and
• I "JOIN" the tables each time I extract data about where to eat prior to a journey.

That is the principle of joining tables in a database!

The syntax will look something like this:
  select * from place join journey on called = start_at
and you can join more that two tables if you wish [example]- one of our customers joins over 20 ...

Once you start linking tables in this way, you come up with some extra questions when you're maintaining the database such as "do we have any journeys where there's no-where to eat at the start", and "can I have a list of all journeys, irrespective of whether I can eat at the starting place or not ..."; those can be achieved using a LEFT JOIN which ensures that there is at least one record on the output for every input in the left hand table, even of there is nothing to match it in the right hand table (the resultant record is NULL padded).

I've set up the above example - table creation and a big variety of joins, with sample output - [here] in our examples directory. And there's a further description - join v left join v right join - [here] on this blog. We cover database design (the MySQL clavour) on our MySQL course, and how to access databases from applications on our various language courses, such as our courses in PHP, Perl and Java.

(written 2010-11-20, updated 2010-12-04)

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

S152 - SQL Primer as Used in MySQL
  [270] NULL in MySQL - (2005-04-06)
  [2240] How do I query a database (MySQL)? - (2009-06-15)
  [3060] INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables - (2010-11-19)
  [4007] Which database should I use? MySQL v SQLite - (2013-02-16)

Back to INSERT, DELETE, REPLACE and UPDATE - changing the content of SQL tables

Previous and next or Horse's mouth home

Forward to Melksham Carnival - getting ready for 2011

Pictures from the Birkenhead to Belfast crossing
On the way to the course this week
Comments in and on Perl - a case for extreme OO programming
Melksham Carnival - getting ready for 2011
Databases - why data is split into separate tables, and how to join them
Object Orientation in an hour and other Perl Lectures
MacBook Air - hardware and system review
Lots of things to do with and within a C++ class
C++ - a complete example with polymorphism, and how to split it into project files