Self joins - Page 5

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:

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:

• MySQL join syntax
• PostgreSQL select syntax
• Introduction to joins
• Database Normalization
• Simple SQL: Getting Started With SQL