JOINS

Joins allows us to aggregate multiple tables into a single result set. So for example we might want names of Plants from our plant database and information from other tables that relates to the plant.

As a more concrete example, we might have a foreign key from our Plant table which relates to the Plant Type (i.e. whether a plant is a Tree, Vegetable, Flower etc.)

CROSS JOIN

Before we look at an example of a JOIN as outlined above, let’s look at a CROSS JOIN. A CROSS JOIN is basically a JOIN of two tables without any where clause, so for example

select p.CommonName, pt.Name 
from Plants p, PlantTypes pt

This type of join is expensive in that it’s simply taking two tables and merging data, and in many ways it’s probably of little in terms of the result set produced. For example in my plant data at the moment I have 29 plants listed (not many I admit, but it’s early days). I also have 4 plant types. The result set of the CROSS join above is 4 * 29 = 116 rows. Basically the result set lists each plant CommonName against each plan type Name.

INNER JOIN

An inner join is generally the most used JOIN whereby we’re going to be looking for all items from one table matching a column to data from another table. So again using our Plants table and Plant Types we might want to see the plant type associated with each plan in our database.

Note: We can create such a join without the INNER JOIN keywords, by default in SQL Server such joins are inner joins anyway

select p.CommonName, pt.Name 
from Plants p inner join PlantTypes pt
on p.Type_Id = pt.Id

So this assumes that the Type_Id is a foreign key into the PlantTypes table and relates to it’s primary key.

FULL OUTER JOIN

A FULL OUTER JOIN will return NULL data. So for example we allowed NULL’s for our Type_Id or there are no matches for a PlantTypes Id then we’ll see NULL values in the output columns, for example the query would look like

select p.CommonName, pt.Name 
from Plants p inner join PlantTypes pt
on p.Type_Id = pt.Id

Our result set may now display CommonName NULL (for example) if a PlantType Id does not
have a matching Plant, i.e. we’ve not added any Trees yet to out Plants table.

Note: The FULL OUTER JOIN syntax is not supported by MySQL

LEFT OUTER JOIN

As we’ve seen with a FULL OUTER JOIN we might have a PlantType which is not yet used in the Plants table and therefore we’ll see a NULL for the column CommonName. However we might also have a plant which doesn’t yet have a Type_Id and hence would have a NULL in this column.

If we don’t want to view plant types which have NULL CommonNames (in other words we only really care about the plants not the types) we may want to see all plants and their plant types regardless of whether they’re NULL, we can use a LEFT OUTER JOIN

select p.CommonName, pt.Name 
from Plants p left outer join PlantTypes pt
on p.Type_Id = pt.Id

In this case we get data from the left table whether or not they have a matching value in the right hand side table.

RIGHT OUTER JOIN

As you’ve guessed a RIGHT OUTER JOIN will return all values from the plants data with a match on the plant type plus those not matching the plan type with no plants associated with it.

select p.CommonName, pt.Name 
from Plants p right outer join PlantTypes pt
on p.Type_Id = pt.Id

SELF JOIN

We’ve seen how to create joins with other tables but actually we can join with the same table. There’s no SELF JOIN keywords, it’s more the concept of joining one table with itself. The most obvious use of such a join is within a hierarchal type of data. Possibly a Plant Type might have a parent Plant Type then one could join against the parent plant type on the same table as the child plant type.