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.
| EmployeeNo |
FirstName |
LastName |
JoinedDate |
DepartmentNo |
BirthDate |
| 101 |
Mike |
James |
3/27/2005 |
1 |
1/5/1980 |
| 102 |
Robert |
Williams |
4/25/2007 |
2 |
3/5/1983 |
| 103 |
Peter |
Paul |
3/21/2007 |
2 |
4/1/1983 |
| 104 |
Alex |
Stuart |
2/1/2008 |
2 |
11/6/1984 |
| 105 |
Robert |
James |
1/4/2008 |
3 |
12/1/1984 |
| EmployeeNo |
Gross |
Deduction |
NetPay |
| 101 |
40,000 |
4,000 |
36,000 |
| 102 |
80,000 |
6,000 |
74,000 |
| 103 |
90,000 |
7,000 |
83,000 |
| 104 |
75,000 |
5,000 |
70,000 |
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
