Understanding SQL Join Types for use in Reporting/Collections within ITMS

Products

• IT Management Suite

Issue/Introduction

Can I get some information on SQL join types for use within reports, collections, or custom queries within the Symantec_CMDB database?

Environment

ITMS 8.x

Cause

Customer education

Resolution

Inner Join
An inner join returns only those records from both tables that have a matching value in the related field. This type of join is by far the most commonly used. In fact, many systems use this type as the default join. To help you visualize what an inner join looks like, think of your tables as intersecting circles. Each circle represents a table within a relationship, and the area where the circles intersect represents the records that have a matching primary and foreign key value. SQL retrieves or acts upon those records—and only those records—where the two circles intersect.

The Outer Joins
An outer join retrieves records where the primary/foreign key values match, but it also retrieves records that do not have a matching value. There are three types of outer joins: left outer, right outer, and full outer. All outer joins retrieve records from both tables, just as an inner join does. However, an outer join retrieves all of the records from one of the tables.

There's another very significant difference between outer joins and the inner join. The order in which you reference the tables may be significant because the first table becomes what's known as the left table, or the one table in a one-to-many relationship. The second table is the right table, or the many table in a one-to-many relationship. What this means is that the SELECT clause order not only dictates how the columns are displayed in the resultset, but that this order also determines which table delivers all of its records. In an inner join, this latter point just doesn't exist.

Left Outer
The left outer join retrieves records from both tables, retrieving all the records from the one side of the relationship—that's the left table—and any records from the right table (or many table) where the primary/foreign key values match. If there are no matching values in the primary/foreign key columns, the join still retrieves all the records from the one side. Consequently, the resulting recordset often appears to have incomplete records.

The following statement illustrates the usefulness of a left outer join:

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY OrderID

This statement returns all the customer records from the Customers table and relates each customer to every order number in the Orders table. By adding the ORDER BY clause, you can easily spot those customers who haven't ordered anything.

Right Outer
The right outer join is similar to the left outer join in that it retrieves all the records from one side of the relationship, but, this time, it's the right or many side. Only records where the primary/foreign keys match are retrieved from the left or one side of the relationship.