Home

Data Joins: Outer Joins

   

Introduction

 

Instead of showing only records that have entries in the child table, you may want your query to include all records, including those that are null. To get this result, you would create an outer join. You have three options.

Left Outer Joins

A left outer join produces all records of the child table, also called the right table. The records of the child table that do not have an entry in the foreign key column are marked as NULL.

To create a left outer join, if you are working in the Table window, in the Diagram section, right-click the line that joins the tables and click the option that would select all records from the child table (in this case, that would be Select All Rows From Persons):

Join

Alternatively, you can replace the TypeOfJoin factor of our formula with either LEFT JOIN or LEFT OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Sexes.SexID, Sexes.Sex
FROM Persons
LEFT OUTER JOIN Sexes
ON Persons.SexID = Sexes.SexID
GO

In both cases, the button in the middle of the line would be added an arrow that points to the parent table. You can then execute the query to see the result. Here is an example:

Join

Notice that the result includes all records of the Persons (also called the right) table and the records that don't have an entry in the SexID column of the Persons (the right) table are marked with NULL.

Right Outer Joins

A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in this case the Sexes table) and shows each record of the child table (in this case the Persons table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Persons records that have a 1 (Female) value for the SexID column. After the first record, the right outer join moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.

To visually create a right outer join in the Table window, after establishing a join between both tables, if you had previously created a left outer join, you should remove it by right-clicking the line between the tables and selecting the second option under Remove. Then, you can right-click the line that joins them and click the option that would select all records from the parent table. In our example, you would click Select All Rows From Sexes.

To create a right outer join in SQL, you can replace the TypeOfJoin factor of our formula with RIGHT JOIN or RIGHT OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Sexes.SexID, Sexes.Sex
FROM Persons
RIGHT OUTER JOIN Sexes
ON Persons.SexID = Sexes.SexID
GO

In both cases, the button on the joining line between the tables would have an arrow that points to the child table. You can then run the query. Here is an example:

Join

Notice that the query result starts with the first record of the parent table, also called the left table (in this case the Sexes table), and lists the records of the child table, also called the right table (in this case the Persons table), that have the entry corresponding to that first record. Then it moves to the next SexID value. Also, notice that there are no NULL records in the Sex.

Practical LearningPractical Learning: Getting Non-NULL Records

  1. In the Data Source window, right-click dsAltairRealtors and click Edit Dataset with Designer
  2. In the designer, right-click the title bar of the Properties table and click Configure...
  3. In the TableAdapter Configuation Wizard, click Query Builder...
  4. To get a list of only properties whose types are known, right-click the line between the tables and click Select all rows from PropertyTypes
  5. Right-click anywhere in the window and click Execute SQL
     
    Query Builder
  6. Notice that the result is the list of tables in order by types (condos, single families, and town homes)
  7. Uncheck all columns

Full Outer Joins

A full outer join produces all records from both the parent and the child tables. If a record from one table does not have a value in the other value, the value of that record is marked as NULL.

To visually create a full outer join, in the Table window, right-click the line between the tables and select each option under Remove so that both would be checked. To create a full outer join in SQL, replace the TypeOfJoin factor of our formula with FULL JOIN or FULL OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
             Sexes.SexID, Sexes.Sex
FROM Persons
FULL OUTER JOIN Sexes
ON Persons.SexID = Sexes.SexID
GO

The button on the line between the tables would now appear as a square. You can then execute the query. Here is an example:

Join

Just as we have involved only two tables in our joins so far, you can create a join that includes many tables.

 

Published on Sunday 10 February 2008

 

Previous Copyright © 2008-2010 FunctionX, Inc. Next