Sharpen Your Basic SQL Server Skills - 24 Apr 2008

Q: What is a self-join?
A: A self-join is a standard SQL Server join that joins a table to itself. You can perform a self-join when all the data you require is in a single table, and the data you need to extract relates to other data in the same table. You join a table to itself using a field or combination of fields that have matching data in different records. To perform a self-join, you must use a table name for each instance of the table, so that each instance has a separate name. Use a self-join when you want to compare values in a column to other values in the same column. The data type of the columns on which the join is made must be the same or you’ll need to cast them to the same type.

For example, in employee information records, each record might have a field for an employee’s ID number and also a field for the supervisor’s ID number. To retrieve a list of employees and their managers you need to join the employee information table to itself.

Q: Is a self-join an inner join or an outer join?
A: A self-join can be either an inner join or an outer join. Let’s look at some examples that show how using either an inner join or an outer join affects the self-join.

First, run the following code to create a table called SaleOnDate with two columns, DateOfMonth and Sold- Qty, and populate it with sample data:

USE TestData  GO  CREATE TABLE SaleOnDate (DateOfMonth INT,  SoldQty INT)  GO  INSERT INTO SaleOnDate (DateOfMonth,  SoldQty)  SELECT 1, 100  UNION ALL  SELECT 2, 150  UNION ALL  SELECT 3, 200  UNION ALL  SELECT 4, 225  UNION ALL  SELECT 6, 250  UNION ALL  SELECT 7, 280  UNION ALL  SELECT 8, 300  GO

The SaleOnDate table contains information about the day of the month and the quantity of items sold on that day. Figure 1 shows the contents of this table, which you can see by running the following query:

SELECT * FROM SaleOnDate

Next, run the following query to find the difference between a named day and the next day:

SELECT sd1.DateOfMonth, (sd2.SoldQty -    sd1.SoldQty) DifferenceInSale    FROM SaleOnDate sd1    INNER JOIN SaleOnDate sd2 ON sd1.    DateOfMonth + 1= sd2.DateOfMonth    GO

This query demonstrates the use of an inner join when joining the SaleOnDate table to itself. As you can see in Figure 2, the result set doesn’t contain information about days 4 and 8, because days 5 and 9 don’t exist in the database. This is because the conditions specified in the join clause haven’t been met.

Next, let’s look at the use of a left outer join, also called a left join, to join the SaleOnDate table to itself. The following code demonstrates the use of a left join when joining the SaleOnDate table to itself:

SELECT sd1.DateOfMonth,    ISNULL(CAST((sd2.SoldQty - sd1.    SoldQty) AS VARCHAR(10)), ‘No Data’)    DifferenceInSale    FROM SaleOnDate sd1    LEFT JOIN SaleOnDate sd2 ON sd1.    DateOfMonth + 1= sd2.DateOfMonth    GO

The result set, shown in Figure 3, now contains information about days 4 and 8. Because days 5 and 9 don’t exist in the database, the result set contains NULL for days 4 and 8. NULL values are displayed as No Data because the ISNULL function was used in the SELECT clause.