# Two tables ... an illustration of the different joins

# In this example (from a MySQL course example I wrote for our delegates)
# we create two tables - one of train journeys, and the other of places
# to eat at the station prior to departure.
#
# Since there are a number of possible journeys from each starting point,
# I used two tables - one for the journeys, and another to give the best
# place at each starting point before the journey. That way, I only have
# one set of data to maintain about best eating places, and no duplication
# of data. I then use a MySQL JOIN clause in my SELECT to link up the tables.
#
# A regular JOIN will ONLY give me results where there is somewhere to eat
# at the departure station. If I don't have anywhere available to eat, or
# if there's somewhere to eat at a station from which I have no journeys, there
# will be no record in the output.
#
# If, on the other hand, I use a LEFT JOIN, my result set will include all of
# the joined records for a regular join and IN ADDITION I'll get an additional
# record for each row in the left hand table that has no match in the right
# hand table, with the joined record being filled in with NULLs. Thus my result
# set will have at least one record in it for each and every record in the
# incoming left hand table.
#
# A RIGHT JOIN, similarly, includes at least one record on the output for
# each record in the incoming right hand set ...
#
# So -
# * left table: places to eat
# * right table: journeys
# means
# * regular join: Journeys where you can eat before travelling
# * left join: all places to eat PLUS all journeys you can make after eating
# * right join: all journeys, and places to eat beforehand where available

drop table if exists journey;
create table journey (
     jid int primary key not null auto_increment,
     start_at text,
     end_at text,
     miles int);

drop table if exists place;
create table place (
     pid int primary key not null auto_increment,
     called text,
     gridref text,
     about text);

insert into journey values (1,"Chippenham","London",95);
insert into journey values (2,"Chippenham","Slough",80);
insert into journey values (3,"Llandod","Hereford",35);
insert into journey values (4,"Hereford","Newport",40);
insert into journey (start_at, end_at, miles) values ("Melksham","Acton",95);
insert into journey (start_at, end_at, miles) values ("Melksham","Chippenham",5);

delete from journey where jid = 4;
replace into journey values (3,"Llandrindod Wells","Hereford",35);
update journey set miles = 96 where jid = 1;

insert into place (called,gridref,about) values
        ("Chippenham","SU635443","Nice Chinese"),
        ("London","TQ543223","Yo Sushi"),
        ("Westbury","ST998665","Tail of Spice"),
        ("Melksham","SU654222","Burger Van");

# Two alternative ways of linking tables ... first shows principle BUT
# second is the 'proper' and more efficient way:

select * from place,journey where called = start_at and miles > 50;
select * from place join journey on called = start_at where miles > 50;

# +-----+------------+----------+--------------+-----+------------+--------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +-----+------------+----------+--------------+-----+------------+--------+-------+
# | 1 | Chippenham | SU635443 | Nice Chinese | 1 | Chippenham | London | 96 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 2 | Chippenham | Slough | 80 |
# | 4 | Melksham | SU654222 | Burger Van | 5 | Melksham | Acton | 95 |
# +-----+------------+----------+--------------+-----+------------+--------+-------+
# 3 rows in set (0.00 sec)
#
# +-----+------------+----------+--------------+-----+------------+--------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +-----+------------+----------+--------------+-----+------------+--------+-------+
# | 1 | Chippenham | SU635443 | Nice Chinese | 1 | Chippenham | London | 96 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 2 | Chippenham | Slough | 80 |
# | 4 | Melksham | SU654222 | Burger Van | 5 | Melksham | Acton | 95 |
# +-----+------------+----------+--------------+-----+------------+--------+-------+
# 3 rows in set (0.00 sec)

# DIFFERENCE BETWEEN MATCH TYPES ...

# All records that match

select * from place join journey on called = start_at;
# or use further / full qualification if there may be conflict
select * from place join journey on place.called = graham.journey.start_at;

# +-----+------------+----------+--------------+-----+------------+------------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +-----+------------+----------+--------------+-----+------------+------------+-------+
# | 1 | Chippenham | SU635443 | Nice Chinese | 1 | Chippenham | London | 96 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 2 | Chippenham | Slough | 80 |
# | 4 | Melksham | SU654222 | Burger Van | 5 | Melksham | Acton | 95 |
# | 4 | Melksham | SU654222 | Burger Van | 6 | Melksham | Chippenham | 5 |
# +-----+------------+----------+--------------+-----+------------+------------+-------+
# 4 rows in set (0.00 sec)
#
# +-----+------------+----------+--------------+-----+------------+------------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +-----+------------+----------+--------------+-----+------------+------------+-------+
# | 1 | Chippenham | SU635443 | Nice Chinese | 1 | Chippenham | London | 96 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 2 | Chippenham | Slough | 80 |
# | 4 | Melksham | SU654222 | Burger Van | 5 | Melksham | Acton | 95 |
# | 4 | Melksham | SU654222 | Burger Van | 6 | Melksham | Chippenham | 5 |
# +-----+------------+----------+--------------+-----+------------+------------+-------+
# 4 rows in set (0.00 sec)

# All records that match PLUS orphans in left / right table

select * from place left join journey on called = start_at;
select * from place right join journey on called = start_at;

# +-----+------------+----------+---------------+------+------------+------------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +-----+------------+----------+---------------+------+------------+------------+-------+
# | 1 | Chippenham | SU635443 | Nice Chinese | 1 | Chippenham | London | 96 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 2 | Chippenham | Slough | 80 |
# | 2 | London | TQ543223 | Yo Sushi | NULL | NULL | NULL | NULL |
# | 3 | Westbury | ST998665 | Tail of Spice | NULL | NULL | NULL | NULL |
# | 4 | Melksham | SU654222 | Burger Van | 5 | Melksham | Acton | 95 |
# | 4 | Melksham | SU654222 | Burger Van | 6 | Melksham | Chippenham | 5 |
# +-----+------------+----------+---------------+------+------------+------------+-------+
# 6 rows in set (0.00 sec)
#
# +------+------------+----------+--------------+-----+-------------------+------------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +------+------------+----------+--------------+-----+-------------------+------------+-------+
# | 1 | Chippenham | SU635443 | Nice Chinese | 1 | Chippenham | London | 96 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 2 | Chippenham | Slough | 80 |
# | NULL | NULL | NULL | NULL | 3 | Llandrindod Wells | Hereford | 35 |
# | 4 | Melksham | SU654222 | Burger Van | 5 | Melksham | Acton | 95 |
# | 4 | Melksham | SU654222 | Burger Van | 6 | Melksham | Chippenham | 5 |
# +------+------------+----------+--------------+-----+-------------------+------------+-------+
# 5 rows in set (0.00 sec)

# JUST orphans in left / right table

# next line is WRONG ...
select * from place left join journey on called = start_at where jid = NULL;

#
# Empty set (0.00 sec)
#

# instead, you want ...
select * from place left join journey on called = start_at where jid is NULL;

# +-----+----------+----------+---------------+------+----------+--------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +-----+----------+----------+---------------+------+----------+--------+-------+
# | 2 | London | TQ543223 | Yo Sushi | NULL | NULL | NULL | NULL |
# | 3 | Westbury | ST998665 | Tail of Spice | NULL | NULL | NULL | NULL |
# +-----+----------+----------+---------------+------+----------+--------+-------+
# 2 rows in set (0.00 sec)

# and
select * from place right join journey on called = start_at where pid is NULL;

# +------+--------+---------+-------+-----+-------------------+----------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +------+--------+---------+-------+-----+-------------------+----------+-------+
# | NULL | NULL | NULL | NULL | 3 | Llandrindod Wells | Hereford | 35 |
# +------+--------+---------+-------+-----+-------------------+----------+-------+
# 1 row in set (0.00 sec)
# http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

# Example that's sorted ...

select * from place right join journey on called = start_at order by called desc, miles;

# +------+------------+----------+--------------+-----+-------------------+------------+-------+
# | pid | called | gridref | about | jid | start_at | end_at | miles |
# +------+------------+----------+--------------+-----+-------------------+------------+-------+
# | 4 | Melksham | SU654222 | Burger Van | 6 | Melksham | Chippenham | 5 |
# | 4 | Melksham | SU654222 | Burger Van | 5 | Melksham | Acton | 95 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 2 | Chippenham | Slough | 80 |
# | 1 | Chippenham | SU635443 | Nice Chinese | 1 | Chippenham | London | 96 |
# | NULL | NULL | NULL | NULL | 3 | Llandrindod Wells | Hereford | 35 |
# +------+------------+----------+--------------+-----+-------------------+------------+-------+
# 5 rows in set (0.00 sec)