Examples - Teradata Database

Example: FROM Clause Used for a Self-Join

The following statement returns employees who have more years of work experience than their department managers:

     SELECT workers.name, workers.yrs_exp, workers.dept_no,
            managers.name, managers.yrs_exp
     FROM employee AS workers, employee AS managers
     WHERE managers.dept_no = workers.dept_no
     AND   UPPER (managers.jobtitle) IN ('MANAGER' OR 'VICE PRES')
     AND   workers.yrs_exp > managers.yrs_exp;

The FROM clause in the preceding statement enables the employee table to be processed as though it were two identical tables: one named workers and the other named managers.

As in a normal join operation, the WHERE clause defines the conditions of the join, establishing dept_no as the column whose values are common to both tables.

The following result is returned:

Example: FROM Clause Left Outer Join

The following example illustrates a left outer join. See Outer Joins. In the example, the skills table lists various skills and the associated codes, and the emp table lists employee numbers and a skills codes.

You can use this query to determine which skill areas do not have assigned employees:

     SELECT skills.skill_name, emp.emp_no
     FROM skills LEFT OUTER JOIN emp ON skills.skill_no=emp.skill_no;

The following result is returned. Notice that nulls are displayed as a QUESTION MARK (?) character, which is how BTEQ reports nulls. For more information about BTEQ, see Basic Teradata Query Reference .

To include all skills in the result, you must specify an OUTER JOIN. An implicit join like this example that uses just the simple FROM clause does not return rows for nulls (that is, when there are no corresponding employees) and would not list doctor or tailor in the above result.

     …
     FROM employee, skills
     …