Teradata - Joins

---

---

Join is used to combine records from more than one table. Tables are joined based on the common columns/values from these tables.

There are different types of Joins available.

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Self Join
• Cross Join
• Cartesian Production Join

INNER JOIN

Inner Join combines records from multiple tables and returns the values that exist in both the tables.

Syntax

Following is the syntax of the INNER JOIN statement.

SELECT col1, col2, col3…. 
FROM  
Table-1 
INNER JOIN 
Table-2 
ON (col1 = col2) 
<WHERE condition>;

Example

Consider the following employee table and salary table.

The following query joins the Employee table and Salary table on the common column EmployeeNo. Each table is assigned an alias A & B and the columns are referenced with the correct alias.

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay 
FROM  
Employee A 
INNER JOIN 
Salary B 
ON (A.EmployeeNo = B. EmployeeNo);

When the above query is executed, it returns the following records. Employee 105 is not included in the result since it doesn’t have matching records in the Salary table.

*** Query completed. 4 rows found. 3 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo   DepartmentNo     NetPay 
-----------  ------------   ----------- 
    101           1            36000 
    102           2            74000 
    103           2            83000 
    104           2            70000

OUTER JOIN

LEFT OUTER JOIN and RIGHT OUTER JOIN also combine the results from multiple table.

• LEFT OUTER JOIN returns all the records from the left table and returns only the matching records from the right table.

• RIGHT OUTER JOIN returns all the records from the right table and returns only matching rows from the left table.

• FULL OUTER JOIN combines the results from both LEFT OUTER and RIGHT OUTER JOINS. It returns both matching and non-matching rows from the joined tables.

Syntax

Following is the syntax of the OUTER JOIN statement. You need to use one of the options from LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN.

SELECT col1, col2, col3…. 
FROM  
Table-1 
LEFT OUTER JOIN/RIGHT OUTER JOIN/FULL OUTER JOIN 
Table-2 
ON (col1 = col2) 
<WHERE condition>;

Example

Consider the following example of the LEFT OUTER JOIN query. It returns all the records from Employee table and matching records from Salary table.

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay 
FROM  
Employee A 
LEFT OUTER JOIN 
Salary B 
ON (A.EmployeeNo = B. EmployeeNo) 
ORDER BY A.EmployeeNo; 

When the above query is executed, it produces the following output. For employee 105, NetPay value is NULL, since it doesn’t have matching records in Salary table.

*** Query completed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo  DepartmentNo     NetPay 
-----------  ------------   ----------- 
    101           1           36000 
    102           2           74000 
    103           2           83000 
    104           2           70000 
    105           3             ?

CROSS JOIN

Cross Join joins every row from the left table to every row from the right table.

Syntax

Following is the syntax of the CROSS JOIN statement.

SELECT A.EmployeeNo, A.DepartmentNo, B.EmployeeNo,B.NetPay 
FROM  
Employee A 
CROSS JOIN 
Salary B 
WHERE A.EmployeeNo = 101 
ORDER BY B.EmployeeNo;

When the above query is executed, it produces the following output. EmployeeNo 101 from Employee table is joined with each and every record from Salary Table.

*** Query completed. 4 rows found. 4 columns returned. 
*** Total elapsed time was 1 second. 
 EmployeeNo  DepartmentNo   EmployeeNo    NetPay 
-----------  ------------  -----------  ----------- 
    101           1            101         36000 
    101           1            104         70000 
    101           1            102         74000 
    101           1            103         83000