Types of join in an SQL query

The Structured Query Language (SQL) allows us to write queries that pull data from more than one table. There are four basic ways of joining two tables together.

'Left' and 'right' just refer to the way that the diagram has been drawn. It's usual to draw the parent table on the left and the child on the right.

The classic example is a small database with a Customers table and an Orders table. Each customer is identified by a unique Customer_ID and each order has a Customer_ID to link it to a particular customer. In this system the four types of query are:

Inner Join

The inner join is the most common and useful type of query. Most of the time you are interested in the records that match in the two tables and this is what the inner join gives you. An inner join of Customers and Orders would return details of all the customers who have placed orders together with the details of the orders that they have placed.

Left outer join

The left outer join is the next most useful because you'll often want to produce a report showing the activities of all customers and even if a customer has placed no orders this month you'll still want to see them on the report.

There is a small snag with the left outer join in that the customers who have no orders won't have any order details. Instead the query will return a Null in all the fields from the Orders table and you'll have to process this and produce something more meaningful to the average user.

Right outer join

A right outer join is the opposite of the left outer join. It's less useful in regular work but can be handy for housekeeping and maintenance.

Staying with the customers and orders example, a right outer join will show you all the orders regardless of whether or not they belong to a customer. These orphan entries are obviously meaningless and records like this should not exist. It's useful to check for them once in a while though - just to make sure that your data integrity routines are still working properly.

Full Outer Join

As its name suggests, a full outer join returns all the details of all the customers regardless of whether or not they have placed an order and all the details of all the orders regardless of whether or not they belong to a customer.