Copyright

SQL: Self-Joins

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Joining a table to itself might sound contradictory. However, it is a useful and powerful tool in the SQL toolbox. We will cover the concept and provide examples of the power of a self-join.

Self-Join

A self-join does what its name implies: it compares a table to itself. It's not a true join, since you're not truly connecting a table to itself. It is, however, a way to compare data in the table to other data in the table. This is helpful when the table is very large and has a lot of columns and data. Other uses include running totals and running counts.

Additionally, you could use a self-join to return a subset of data that may need to be extracted from the table and saved in a different table, especially if the table is becoming large and unwieldy.

If we want to find out which employees have the same job code:


SELECT DISTINCT e1.employeeName, e1.jobTitle, e1.jobCode
FROM Employee AS e1, Employee AS e2
WHERE e1.jobCode = e2.jobCode
AND e1.employeeName <> e2.employeeName
ORDER BY e1.jobCode, e1.employeeName


The DISTINCT clause in the statement ensures that you only get one row per value returned. Since you are querying the same table twice, it is possible that you could get duplicate rows of data. It's not necessarily wrong, but it can cause confusion when you only want one row per match.

Aliases

Even though the query is being completed against a single table, we need to make the system act like it's working on more than one table. We've given aliases for each instance of the table: e1 and e2. It's a way to state that you want to query Employee twice. If we didn't specify these aliases, the system wouldn't be able to complete the query.

Next, there is another step that completes the cycle. Notice the use of the AS in this statement. Since we're querying the same table we have to tell SQL which parts to evaluate. The aliases help us (and the database system) keep everything straight!

Other Examples

There are a few other applications for a self-join. These include running counts and running totals.

Running Count

In this example, we create a running count to count items based on the self-join comparison.


SELECT count(p1.itemID) AS itemNum, p1.itemName, p1.itemID
FROM Products p1
  INNER JOIN Products p2
  ON p1.itemID >= p2.itemID
GROUP BY p1.itemName, p1.itemID
ORDER BY 1;


The result of the query would look something like the following, with the itemNum column being the temporary column created in order to show the running count of the database:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support