Example: FROM Clause Left Outer Join - Teradata Vantage

The following example shows a left outer join. See Outer Joins.

The skills table contains skill numbers and associated names:

The emp table contains employee numbers and skills numbers:

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. Nulls are displayed as a QUESTION MARK (?) character, which is how BTEQ reports nulls. For more information about BTEQ, see Basic Teradata® Query Reference, B035-2414.

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

…
FROM employee, skills
…