Teradata Joins

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

There are different types of Joins available in Teradata.

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 two tables, such as the student table and attendance table.

Student Table:

RollNo	FirstName	LastName	BirthDate
1001	Mike	Richard	1/2/1996
1002	Robert	Williams	3/5/1995
1003	Peter	Collin	4/1/1994
1004	Alexa	Stuart	11/6/1995
1005	Robert	Peterson	12/1/1997

Attendance Table:

RollNo	present	Absent	%
1001	200	20	90%
1002	160	60	72%
1003	150	70	68%
1004	210	10	95%

The following query joins the student table and attendance table on the common column Rollno. Each table is assigned an alias A & B, and the columns are referenced with the correct alias.

SELECT A.RollNo, B.% 
FROM  
Student A 
INNER JOIN 
Attendance B   
ON (A.RollNo = B.RollNo);

When the above query is executed, it returns the following records. Rollno 1005 is not included in the result since it doesn't have matching records in the attendance table.

 Rollno               %
               90%
               72%
               68%
               95%

OUTER JOIN

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

LEFT OUTER JOIN:It returns all the records from the left table and returns only the matching records from the right table.
RIGHT OUTER JOIN:It returns all the records from the right table and returns only matching rows from the left table.
FULL OUTER JOIN:It 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. We 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 the Student table and matching records from the attendance table.

FROM  
Student A 
LEFT OUTER JOIN 
Attendance B 
ON (A.RollNo = B.RollNo) 
ORDER BY A.RollNo;

When the above query is executed, it produces the following output. For student 1005, % value is NULL, since it doesn't have matching records in the attendance table.

Rollno               %
               90%
               72%
               68%
               95%
                 -

CROSS JOIN

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

Cross Join is a Teradata specified join, which is equivalent to the product join. There will not be an "ON" keyword in the Cross Join.

Syntax

Following is the syntax of the CROSS JOIN statement.

Following is the syntax of the CROSS JOIN statement.
SELECT col1, col2, col3?. 
FROM  
Table-1 
CROSS JOIN 
Table-2 
<WHERE condition>;

Example

Consider the following example of the CROSS JOIN query.

SELECT A.RollNo, B.RollNo, B.% 
FROM  
Student A 
CROSS JOIN 
Attendance B 
WHERE A.RollNo = 1001 
ORDER BY B.RollNo;

When the above query is executed, it produces the following output. RollNo 1001 from the Student table is joined with each and every record from Attendance Table.

Rollno            Rollno                           %
1001                1001                            90%
1001                1002                            72%
1001                1003                            68%
1001                1004                            95%

Next TopicTeradata SubQueries

← prev next →