Using Self-Joins in SQL

Comments

July 12, 2006 at 6:00 am

(1) Thomas Ewald says:

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!