Learning SQL - Design Tips - Outer Joins.

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Show me all the parents!

We've dealt with basic joins (also called inner joins but not by me!). Now let's have a look at outer join. We want a list of all our Parents and their Children if they have any. The following query will show us any parents that DO have children:

select QiSQL_Parents.first_name,
QiSQL_Parents.last_name ,
QiSQL_Children.first_name ,
QiSQL_Children.last_name
from QiSQL_Parents ,
QiSQL_Children
where QiSQL_Parents.id = QiSQL_Children.parent_id;

As you can see, only Susan Brown is included. That seems reasonable as only Susan would saisfy the WHERE clause in our SQL query. But we have three parents in our QISQL_Parents table! You may wonder why we have parents without children, this is a very normal situation in a relational database, the parents MUST be set up before the children, parents may register for our school before their children are even born ( it IS a very good school!).

So we need to use a technique called an Outer Join to display the full list of parents.

Outer Joins

You may have left outer joins or right outer joins in SQL. But in SQLite you may only use LEFT OUTER JOINS, so that is what we will focus on here.

A LEFT OUTER JOIN means that our query will return all the rows from our 'left' table even if the rows in our 'right' table do not match the join condition.

Here is the SQL query we need to use:

select QiSQL_Parents.first_name as Parent_First_Name,
QiSQL_Parents.last_name as Parent_Last_Name,
QiSQL_Children.first_name Child_First_Name,
QiSQL_Children.last_name Child_Last_Name
from QiSQL_Parents LEFT OUTER JOIN QiSQL_Children
ON QiSQL_Parents.id = QiSQL_Children.parent_id;

The 'LEFT' table in this query is QiSQL_Parents, so the query will return ALL the rows in this table even if the join condition on the 'RIGHT' table QiSQL_Children table is not satisfied.

Not the use of 'ON' to specify the join condition.

Also, in this query we have used 'as' to show that Parent_First_Name and Parent_Last_Name are column aliases, but we have omitted 'as' for the Child_First_Name and Child_Last_name column aliases - 'as' is optional, you may like to uses it for clarity, it's up to you.

Dealing with Nulls

As you will see the results of our above query show data look like this:

Parent_First_name Parent_Last_Name Child_First_Name Child_Last_Name
John Smith    
Harold Jones    
Susan Brown Peggy Brown
Susan Brown Lucille Brown
Susan Brown Harry Brown
Susan Brown Quentin Brown

Where the join conditon is not met, SQL inserts a NULL value in the resulting output. This could be very confusing so we can use COALESCE to susbstitute NULL values with a specified substitution value: The syntax is select coalsece ( column , 'substitute value') from ...

select QiSQL_Parents.first_name as Parent_First_Name,
QiSQL_Parents.last_name as Parent_Last_Name,
coalesce ( QiSQL_Children.first_name,'No Kids!' ) Child_First_Name,
coalesce (QiSQL_Children.last_name , 'Honestly - Kidless' ) Child_Last_Name
from QiSQL_Parents LEFT OUTER JOIN QiSQL_Children
ON QiSQL_Parents.id = QiSQL_Children.parent_id;

Now we see a much more satisfactory output when we run our query:

Parent_First_name Parent_Last_Name Child_First_Name Child_Last_Name
John Smith No Kids! Honestly - Kidless
Harold Jones No Kids! Honestly - Kidless
Susan Brown Peggy Brown
Susan Brown Lucille Brown
Susan Brown Harry Brown
Susan Brown Quentin Brown

It would be very boring typing all that in everytime we wanted to see our parents and children so why not create a view?

create view qisql_v_parents_children as
select
QiSQL_Parents.first_name as Parent_First_Name,
QiSQL_Parents.last_name as Parent_Last_Name,
coalesce ( QiSQL_Children.first_name,'No Kids!' ) Child_First_Name,
coalesce (QiSQL_Children.last_name , 'Honestly - Kidless' ) Child_Last_Name
from QiSQL_Parents LEFT OUTER JOIN QiSQL_Children
ON QiSQL_Parents.id = QiSQL_Children.parent_id;

Now, we can just type:

select * from vw_parents_children;

Outer joins are a very important concept to understand, you may like to look at this very nice Wikipedia entry covering joins in much greater detail.