Programming Inner and Outer Joins in a Query

Using VBScript to Extract Data from more than One Database Table

Jan 9, 2009

SQL queries can extract data from single or multiple tables. How that data is extracts greatly affects what the programmer can obtains - and it all comes down to the join

It is easy for a database user to extract information from a single table by using simple SQL queries. However, the database user can also use a SQL query's join clause to combine information from more than one table on the database. There are two types of join clauses:

Each one handles the data in a slightly different way. However, if a programmer combines this with a programing language such as Microsoft Visual Basic's VBScript then they can produce very powerful applications - and they can do it very easily.

The SQL Inner Join

The SQL inner join is probably the most common join - it simply combines the contents of two tables base on common information, and will only return those records with matching data. So, to understand this better, the starting point can be a Microsoft Access database (for example c:\sales.mdb) with two tables:

Once the tables are created then they can be populated by using the SQL insert statement with VBScript:

dim datasource : datasource = "provider=microsoft.jet.oledb.4.0;" _

& "data source=c:\sales.mdb"

dim db : set db = createobject("adodb.connection")

db.open datasource

db.Execute "insert into customer (id, surname,firstname)" _

& " values (1,'smith','fred')"

db.Execute "insert into customer (id, surname,firstname)" _

& " values (2,'jones','jane')"

db.Execute "insert into customer (id, surname,firstname)" _

& " values (3,'brown','john')"

db.Execute "insert into orders (id, customer_id) values (1,1)"

db.Execute "insert into orders (id, customer_id) values (2,1)"

db.Execute "insert into orders (id, customer_id) values (3,2)"

The tables will contain three customers: one with two orders; one with one order; and one with no orders at all. This data can now be used to demonstrate a query using an inner join:

dim sql : sql = _

"select count(orders.id), customer.firstname, customer.surname" & vbcr _

& "from customer, orders" & vbcr _

& "where orders.customer_id = customer.id" & vbcr _

& "group by customer.firstname, customer.surname"

dim rs : set rs = createobject("adodb.recordset")

rs.open sql, connection

In this example the recordset will be populated with the results from the query and it will contain only matching records from both tables:

2 fred smith

1 jane jones

It is immediately obvious that one customer, John Brown, is missing (since he has no orders). In order for all records to be seen the programmer must run to the SQL outer join.

The SQL Outer Join

The SQL outer join enables a database programmer to combine the contents of tables even if some of the information is missing, and can be one of two types:

Both left and right outer joins actually do the same job - they both return all of the records from one table combined with any matching records from a second. The left or right statement simply defines which of the two tables is the master and which is the slave. An example of a left join is:

sql = _

"select count(orders.id), customer.firstname, customer.surname" & vbcr _

& "from customer" & vbcr _

& "left join orders" & vbcr _

& "on orders.customer_id = customer.id" & vbcr _

& "group by customer.firstname, customer.surname"

rs.open sql, connection

Here all of the records from the customer table will be included along with any matching records from the orders table, so that the recordset will contain:

2 fred smith

1 jane jones

0 john brown

sql = _

"select count(orders.id), customer.firstname, customer.surname" & vbcr _

& "from orders" & vbcr _

& "right join customer" & vbcr _

& "on orders.customer_id = customer.id" & vbcr _

& "group by customer.firstname, customer.surname"

In this way the programmer can have complete control over the records that are obtained from the database.

Summary

The outer join is further subdivided into left and right giving the programmer a great deal of flexibility in the way that they obtain information from a set of database tables.

The copyright of the article Programming Inner and Outer Joins in a Query in Database Programming is owned by Mark Alexander Bain. Permission to republish Programming Inner and Outer Joins in a Query in print or online must be granted by the author in writing.