Understand Joins in SQL

Understand Joins in SQL

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.

Self Joins

·         There is one last type of join that must be considered, which is a self join. A self join is merely a join from a table to itself.

EMPLOYEE TABLE -EmployeeName -SupervisorID

·         In this situation, in order to find which employees are supervised by a given employee, a self join would be required.

·         Hopefully this clarifies the basic tenets of joins, as they are one of the primary features of SQL that makes it such a powerful database language. Make sure you use the proper join for your given situation.