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? Here's a simple example.
Explore Databases
©2012 About.com. All rights reserved.
A part of The New York Times Company.
In the second example, you’ll want to change “FROM employees e1, employees e1″ to “FROM employees e1, employees e2″.
——————————-
We could use this SQL query:
SELECT last_name, first_name
FROM employees
WHERE city in
( SELECT city
FROM employees
WHERE last_name=”Chapple”
AND first_name=”Mike” c)
Or we could simplify the query using a nested join, as shown below:
SELECT e1.last_name, e1.first_name
FROM employees e1, employees e1
WHERE e1.city = e2.city
AND e2.last_name=”Chapple”
AND e2.first_name=”Mike”
Give it a try the next time you’re composing a SQL query!