Single table SQL select statements are rather easy to write. However, business requirements often dictate that more complex queries must be written. For example, “find all orders for each customer, and display the products for each order”. Now, in this particular situation, it would be likely that there is a customer table, an order table, and an order_line table (the last would be to resolve a possible many-to-many record relationship). For those who are slightly more familiar with SQL, it is readily apparent that a table join, actually, two table joins will be required for this query. Let’s look at some sample code.
- SELECT customer.customerID, order.order_id, order_line.order_item
- FROM customer
- INNER JOIN order
- ON customer.customerID = order.customerID
- INNER JOIN order_line
- ON order.orderID = order_line.orderID;
Alright, simple enough. For those who don’t know, the code above is an inner join. More specifically, the code above is an equi-join.
Let’s define the various types of joins.
Inner Joins: The basic purpose of inner joins is to return matching records.
Outer Joins: Outer joins do not require each record to have a matching record.
- Left outer join: A left outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from the left table, in this case, A.
- Right outer join: A right outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from the right table, in this case, B.
- Full outer join: A full outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from both tables.