17.05 - Example: FROM Clause Left Outer Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

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.

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. For more information about BTEQ, see Basic Teradata® Query Reference, B035-2414.

skill_name

----------

emp_no

------

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

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
     …