Database Design and SQL. Specialized Joins

SQL tutorial

Previous page Previous     TOC Contents     Next pageNext

Lesson 8 - Specialized Joins

Using aliases aka 'nicknames'

An alias is a nickname, a second name given to an object. In SQL there are a few occasions where you may want to use aliases for tables.

Case 1: you have 2 tables with the same field names. It happens frequently and there is no particular problem with it. We have been using different names, with prefixes, for all columns but not everyone does that.

Suppose that you used EmpID in both the TS_Master and Employee tables. Now, when you do a join on the tables, like this:

SELECT tm_Num, tm_Date, EmpID,
        e_Fname, e_Lname, e_Tel
                FROM Employee, TS_Master
                        WHERE EmpID = EmpID
                         AND EmpID = 'A1111';

You soon run into all kinds of problems and all of them will mention something about ...ambiguous reference... which means that the system doesn't know what the heck you're talking about - it can't figure out which EmpID you are refering to because there are 2 of them.

The solution is to add the table names, with dot notation, to the fields which are ambiguous:

SELECT tm_Num, tm_Date, TS_Master.EmpID,
        e_Fname, e_Lname, e_Tel
                FROM Employee, TS_Master
                        WHERE TS_Master.EmpID = Employee.EmpID
                         AND TS_Master.EmpID = 'A1111';

Now, adding Table_name. to all fields is standard SQL syntax. If you look at SQL code generated by the Access query wizard, you will see that it is done all the time, regardless of whether the name is ambiguous or not. But in most applications where field names are all different you don't bother doing it because it's just too much extra work.

Which brings me to my next point. If you're a typical programmer, you will want to save every keystroke you can. Typing table names all over the place is a pain. To avoid it, use aliases for the tables - the alias is a name that you give the table in the FROM clause and then you use it everywhere else in the statement:

SELECT tm_Num, tm_Date, T.EmpID,
        e_Fname, e_Lname, e_Tel
                FROM Employee E, TS_Master T
                        WHERE T.EmpID = E.EmpID
                         AND T.EmpID = 'A1111';

OK, so it may not be an earth-shattering improvement but it is an improvement.

The standard syntax for using an alias is:

SELECT Alias1.field, Alias2.field, .....
        FROM Table_name AS Alias1, Table_name AS Alias2 ...;

The 'AS' operator is optional and most people don't use it.

Don't worry about using the alias in the SELECT clause before it's been named in the FROM clause - that's the way it works.


Case 2: You have to join 2 tables to one primary key:

In our application, Department Head, the column d_Head, contains an employee_ID. If you want to create a query to display both the employee's name and the department head's name, you will have to join 2 Fk to the Employee Pk. But the rules don't allow for 2 joins to one key. The solution is to use an alias for the Employee table - in fact, consider the Employee as if it were 2 tables, one for the employee's name and the other one for the department head's name.

SELECT tm_Num, tm_empID, E1.e_Lname, E1.e_Fname,
         d_deptname, d_Head, E2.e_Lname, E2.e_fName
                 FROM TS_Master, Department, Employee E1, Employee E2
                         WHERE tm_EmpID = E1.e_ID
                         AND d_Head = E2.e_ID
                         AND E1.e_Dept = d_DeptNum;

The same technique applies when you must show an employee's name and a project leader's name from timesheet information. In the application, project leaders are only identified in the Project table by their employee ID. As in the previous example, you have to use an alias for the Employee table to get both employee name and project leader name in the query.

SELECT tm_Num, tm_empID, E1.e_Lname, E1.e_Fname, td_ProjNum,
         p_Title, p_Leader, E2.e_Lname, E2.e_fName
                 FROM TS_Master, TS_Detail, Project, Employee E1, Employee E2
                         WHERE tm_Num = td_Num
                        AND p_Number = td_ProjNum
                        AND tm_EmpID = E1.e_ID
                        AND p_Leader = E2.e_ID;



The joins we have been doing so far have all been Inner Joins. That is the kind of join that is done by default when you join two or more tables. It means that the only rows displayed by the query are those where all the columns asked for by the query contain valid data. But what if some columns are null and you still want to see them? There is another form of join for those cases and it's called an Outer Join.

In the previous lesson we had a case where we wanted to see all the departments involved in projects in Germany. The only way to get that is by joining all the tables involved in providing the chain from 'Project' to 'Department'. Since all the joins are inner joins by default, only those joins where both columns exist will be displayed.

Let's look at the example again, but simplified.

Query: List all countries for which there are timesheets.

SELECT p_Number, p_Title, p_Country, td_Num, td_Hours
      FROM Project, TS_Detail
            WHERE p_Number = td_ProjNum;

You get the list of all countries for which timesheets have been submitted:

However, if you want to see all countries for which there are projects, including those with no timeshheets, you must do an outer join which, in Access, is written as LEFT JOIN because you will see all the rows from the table named named on the left in the JOIN clause:

SELECT p_Number, p_Title, p_Country, td_Num, td_Hours
      FROM Project LEFT JOIN TS_Detail
            ON Project.p_Number = TS_Detail.td_ProjNum;

For some reason Access insists on having the table_name included in the ON clause.

You get the list of all countries even if timesheets have not been submitted:

Outer joins are not used a lot in SQL but they do have a few crucial applications.

If you ever have to design any kind of scheduling or reservations system, you will have to use Outer Joins.

For example, a Doctor's office where you have an Appointments table joined to a Patients table, the only way you can see both the booked slots and the empty slots in a query is to use an Outer Join.

For Hotel reservations where you have to see the rooms which are occupied as well as those that are free, you will have to Outer Join the tables Rooms and Customers.

If you haven't found the database resource you're looking for,
use our Google Search box for more information.



Home | Tutorials | Contact | Sitemap | Add URL | Privacy policy