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.
- VARCHAR first_name
- VARCHAR last_name
- VARCHAR city
- VARCHAR state
- VARCHAR zip PRIMARY KEY
SELECT last_name, first_name
WHERE zip in
( SELECT zip
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
Youll 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!