Just like Teradata set operators, you can combine records from multiples tables using Teradata joins. Teradata join syntax is similar to other database SQL joins. You can execute SQL queries with different join types on Teradata machine with little or without any change.
There are many advantages of using SQL joins, for example, combine columns from multiples tables, update tables using joins.
The best way to understand the Teradata joins is to get to know the SQL joins and then consider the join strategies that is used by Teradata database to resolve them.
Test Data
Below are the tables and associated data that we will be using to demonstrate different joins available in Teradata database:
Student:
| ID | Name | Location |
| 1 | Bill | London |
| 2 | Ram | Mumbai |
| 3 | Roy | Bangalore |
| 4 | Nam | Mumbai |
| 5 | ramie | Bangalore |
Department:
| Dpt_id | Name | Sid |
| 1000 | CSE | 1 |
| 1001 | Maths | 1 |
| 1002 | Physics | 2 |
| 1003 | Chem | 3 |
Different Teradata Joins
Teradata supports all join types that are supported in SQL-99. Following are the different join types:
- Inner join
- Left [outer] join
- Right [outer] join
- full outer join
- Cross join
Teradata Inner Join or Equijoin
The inner join is also represented as simply “join” in SQL queries. This join is also referred to as “equijoin”. An equijoin is a join where the join condition is an equality operator ( = ), and each term is a simple column reference. Inner join combines two tables based in the joining condition and return columns specified in the SELECT clause that satisfy the joining condition. Teradata inner join return common values from two tables.
Inner Join Syntax
SELECT t1.col1, t2.col1
FROM table2 as t1
JOIN table1 as t2
ON (t1.common_column = t2. common_column);Inner Join Example
SELECT t1.ID, t2.DID
FROM student as t1
JOIN department as t2
ON (t1.id = t2. sid);Teradata Left Outer Join
Left outer join returns all records from the left table along with any matched records from right table. Unmatched records from right tables will be returned as NULL.
Related Articles:
Left Outer Join Syntax
SELECT t1. col1, t1.col2, t2.col1, t2.col2
FROM table1 as t1
LEFT OUTER JOIN table2 as t2
ON (t1.matching_col = t2.matching_col);Left Outer Join Examples
SELECT t1.ID,t1.name, t2.DID, t2.name
FROM student as t1
LEFT OUTER JOIN department as t2
ON (t1.id = t2. sid);Teradata Right Outer Join
Right outer join returns all records from the right table along with any matched records from left table. Unmatched records from left tables will be returned NULL by default.
Right Outer Join Syntax
SELECT t1. col1, t1.col2, t2.col1, t2.col2
FROM table1 as t1
RIGHT OUTER JOIN table2 as t2
ON (t1.matching_col = t2.matching_col);Right Outer Join Example
SELECT t1.ID,t1.name, t2.DID, t2.name
FROM student as t1
RIGHT OUTER JOIN department as t2
ON (t1.id = t2. sid);Teradata Full Outer Join
The full outer join is a combination of both left and right outer join. Full outer join returns all rows from the both tables for columns listed in SELECT clause that satisfy the join condition. The unmatched rows from both tables will be returned as a NULL value.
Full Outer Join Syntax
SELECT table1. col1, table1.col2, table2.col1, table2.col2
FROM table1
FULL OUTER JOIN table2 ON (table1.matching_col = table2.matching_col);Full Outer Join Example
SELECT t1.ID,t1.name, t2.DID, t2.name
FROM student as t1
FULL OUTER JOIN department as t2
ON (t1.id = t2. sid);Teradata Cross Join
Cross join returns all possible combinations of matching values. This type of join produces a result set with all combinations of rows from the left and right tables.
Cross Join Syntax
SELECT t1. col1, t1.col2, t2.col1, t2.col2
FROM table1 as t1
CROSS JOIN table2 as t2;Cross Join Example
SELECT t1.ID,t1.name, t2.DID, t2.name
FROM student as t1
CROSS JOIN department as t2;Hope this helps ?