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 statement is processed by first selecting managers rows that contain a value of either ‘MANAGER’ or ‘VICE PRES’ in the jobtitle column. These rows are then joined to the workers rows using a merge join operation with this join condition:

A workers row must contain a dept_no value that matches the dept_no value in a managers row.

The matching workers row must also contain a yrsexp value that is greater than the yrs_exp value in the managers row.

The following result is returned:

name ----	yrsexp ------	dept_no -------	name ----	yrsexp ------
Greene W	15	100	Jones M	13
Carter J	20	200	Watson L	8
Aguilar J	11	600	Regan R	10
Leidner P	13	300	Phan A	12
Ressel S	25	300	Phan A	12

Example : FROM Clause Left Outer Join

The following example illustrates a left outer join (see “Outer Joins” on page 235). In the example, the skills table lists various skills and the associated codes, and the emp table lists employee numbers and a skills codes.

skills			emp
skill_no	skill_name		emp_no	skill_no
1	baker		6123	1
2	doctor		6234	1
3	farmer		6392	3
4	lawyer		7281	5
5	mason		7362	4
6	tailor		6169	1

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 (see Basic Teradata Query Reference for more information about BTEQ).

skill_name ----------	emp_no ------
baker	6123
baker	6234
baker	6169
doctor	?
farmer	6392
lawyer	7362
mason	7281
tailor	?

In order 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 

     …

For information about:

Using the FROM clause with temporal tables, see “FROM Clause” in Temporal Table Support.

Self‑joins, see “Self‑Join” on page 234.

Outer joins, see “Outer Joins” on page 235.

Table functions, see “CREATE FUNCTION (Table Form)” in SQL Data Definition Language.

How to qualify column and table specifications, see SQL Fundamentals.