Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Feb 28, 2002

Self joins - Page 5

By Ian Gilfillan

For this exercise, we first need to INSERT another tour guide, as follows:

INSERT INTO tour_guides 
  VALUES('999','Jon Qwelane',30,5)

Now consider another request. We want to find the names of all the employees who have the same hourly rate as Siyabonge Nomvete. Again, we can do this with a nested select:

SELECT employee_name 
  FROM tour_guides 
  WHERE hourly_rate IN 
  (select hourly_rate 
     from tour_guides 
     where employee_name='Siyabonge Nomvete')

But again, a join is preferable. In this case it will be a self-join, as all the data that we need is in the one table--tour_guides. So, we could use the following, more efficient, query:

SELECT e1.employee_name 
       FROM tour_guides e1,tour_guides e2 
       WHERE e1.hourly_rate=e2.hourly_rate AND 
             e2.employee_name='Siyabonge Nomvete'

This returns:

employee_name
Siyabonge Nomvete
Jon Qwelane

There are a few important points to notice here. We could not have used the query, as some of you may have thought,

SELECT employee_name 
       FROM tour_guides 
       WHERE employee_number=employee_number AND 
             employee_name ='Siyabonge Nomvete'

The reason is that we need to see the table as two separate tables to be joined. This query only returns "Siyabonge Nomvete", satisfying the final condition. In order to make the DBMS see the query as a join, we need to provide an alias for the tables. We give them the names e1 and e2. Also important is why we use e1 in SELECT e1.employee_name and e2 in e2.employee_name='Siyanbonge Nomvete'. These 2 have to come from the 2 'different' versions of the table. If we chose the employee_name from the same table that we impose the condition: WHERE employee_name='Siyabonge Nomvete', of course we're on'y going to get that one result back.

Good luck with your joins--remember to keep it as simple as possible for your DBMS, as few as possible nested selects, and you'll see the benefits in your applications!

Additional Resources:



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date