Using Self-Joins in SQL

Did you know that you can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table? Let's take a look at an example.

In our database we have the employees table shown below and we want to obtain a list of all employees who live in the same town as your About.com Guide to Databases.

Table: Employees

• VARCHAR first_name
• VARCHAR last_name
• VARCHAR city
• VARCHAR state
• VARCHAR zip PRIMARY KEY

We could use this SQL query:

SELECT last_name, first_name
FROM employees
WHERE zip in
( SELECT zip
FROM employees
WHERE last_name="Chapple"
AND first_name="Mike")


Or we could simplify the query using a nested join, as shown below:

SELECT e1.last_name, e1.first_name
FROM employees e1, employees e2
WHERE e1.zip = e2.zip
AND e2.last_name="Chapple"
AND e2.first_name="Mike"


You’ll undoubtedly find that using self-joins can simplify many SQL queries that make multiple references to the same table. Relational databases that perform query optimization are also capable of providing great performance enhancement for queries written in this way.

Give it a try the next time you're composing a SQL query!