When writing a PHP based site – developers often also have to integrate with a database (otherwise the site tends not to be very exciting!) and a popular technology for this is a flavour of SQL. Rather than go through the basics of database queries using SQL (which if enough people ask me for – I certainly will do in the future) I have decided to write specifically about the mechanics of the JOIN command. The SQL Join command allows us to pull and combine data from two or more tables, where the result can be modified by specifying different data relationships between the tables.
I have found that using the SQL Join command has saved me a lot of messing around with multiple queries within loops and such like.
For this example we be looking at a system containing customer details matching to addresses. So, our two tables (and example data) will be:
customers:
ID | firstname | lastname |
1 | Robert | Basden |
2 | Simon | Ward |
3 | Damien | Kelly |
addresses:
ID | address | customerID |
1 | 11 Test Street, Berkshire | 1 |
2 | 24 Sparrow Road, London | 1 |
3 | 1a Palmer Place, Berkshire | 2 |
4 | 33 Spooner Street, Cohog | 5 |
The different types of join we can use are: INNER JOIN, LEFT JOIN, RIGHT JOIN or FULL JOIN (Note: INNER JOIN is the same as JOIN). Lets look at each type of join individually to see how this effects our result set.
Inner Join
So the SQL we will use to perform this join will be:
select customers.firstname, customers.lastname, addresses.address from customers inner join addresses on customers.ID = addresses.customerID; |
From this our result set will look like this:
firstname | lastname | address |
Robert | Basden | 11 Test Street, Berkshire |
Robert | Basden | 24 Sparrow Road, London |
Simon | Ward | 1a Palmer Place, Berkshire |
Damiens record has been omitted from the result and also the address 33 Spooner Street becuase neither has a match in the opposing table, the inner join will ONLY return results that have matching results in all the tables you are joining on.
Left Join
select customers.firstname, customers.lastname, addresses.address from customers left join addresses on customers.ID = addresses.customerID; |
The SQL is the same – but we are now using the left join command rather than inner join, this will give us a similar result set to above BUT any rows in the left table (the table specified in the from statment of the SQL) that do not have matches in the right table (the table specified in the left join statement of the SQL) will now also be included in our result set:
firstname | lastname | address |
Robert | Basden | 11 Test Street, Berkshire |
Robert | Basden | 24 Sparrow Road, London |
Simon | Ward | 1a Palmer Place, Berkshire |
Damien | Kelly |
Right Join
The final two types of join work in the ways you would expect them two by their names, right join will give a similar result set to our first result set but this time any tables in the right table (addresses) without matches in the left will also be displayed:
select customers.firstname, customers.lastname, addresses.address from customers right join addresses on customers.ID = addresses.customerID; |
firstname | lastname | address |
Robert | Basden | 11 Test Street, Berkshire |
Robert | Basden | 24 Sparrow Road, London |
Simon | Ward | 1a Palmer Place, Berkshire |
33 Spooner Street, Cohog |
Full Join
This will give us all records pertaining to the join – whether they have matches in the opposing table or not:
select customers.firstname, customers.lastname, addresses.address from customers full join addresses on customers.ID = addresses.customerID; |
firstname | lastname | address |
Robert | Basden | 11 Test Street, Berkshire |
Robert | Basden | 24 Sparrow Road, London |
Simon | Ward | 1a Palmer Place, Berkshire |
Damien | Kelly | |
33 Spooner Street, Cohog |
With joins the rest of the SQL syntax remains the same – so for example you can still specify the fields required (using *, as, count, etc… style commands) and also use order, limit, etc.