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:

addresses:

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:

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:

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;

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;

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.

Rob Basden

Rob founded a web design company company which was acquired by Linear Blue in 2009 in order to bring web development to bring professional web development services in-house. After graduating from Warwick University with a bachelors degree in Computer Systems Engineering, Rob entered the world of web development. He began by supporting a development team then moved on to running his own team as development director. His skills include extensive knowledge of PHP / HTML / Javascript / CSS / Ajax methodologies and Flash / Actionscript.

More Posts