1. Home
  2. Computing & Technology
  3. Databases

Using Self-Joins in SQL

By , About.com Guide

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!
Explore Databases
About.com Special Features

Holiday Central

What to eat, where to go, fun things to do and how to save money on the perfect gifts. More >

Family Tech Center

Stay connected and entertained with reviews on tips on the latest HDTVs, cellphones and more. More >

  1. Home
  2. Computing & Technology
  3. Databases
  4. Learning SQL
  5. Using Self-Joins in SQL>

©2009 About.com, a part of The New York Times Company.

All rights reserved.