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
'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:
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
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.