Inner Joins in Teradata.

As discussed previously, in Teradata we have the following types of Inner Joins. 

  1. Ordinary Inner Join
  2. Cross Join
  3. Self-Join

Let’s discuss how they are different from each other and how to use them.

Ordinary Inner Join: - As you know, JOIN allows you to select common columns and rows from two or more tables/views, similarly in an inner join, common data from two or more tables/views are returned which meets this specific common conditions. Let’s understand this concept with the help of SET rules in Algebra. We have the following two sets:

A = {1, 2, 3, 4, 5}

B = {4, 5, 6, 7, 8}

When you do a JOIN between A and B considering them as tables, only the common element present in them would be returned. In this case it is {4, 5}. This can be derived by the set rule “Intersection”.

So, “A JOIN B” is similar to “A intersection B”. Remember that by default all the Joins are Inner Joins. Following are the ways you can code Joins.

SELECT <table_name(s).column name(s)>
FROM < table_name1 > INNER JOIN < table_name2 >
ON <table_name1.column name> = <table_name2.column name>

SELECT <table_name(s).column name(s)>
FROM < table_name1 > JOIN < table_name2 >
ON <table_name1.column name> = <table_name2.column name>

SELECT <column name(s)>
FROM < table_name1 >, < table_name2 >
WHERE < table_name1.column name1> =  < table_name2.column name2>

Cross Join: - Cross Joins are also known as unconstrained joins (where a “WHERE” clause between the joined tables is not specified/needed). The result of an unconstrained join is also referred to as a Cartesian product. Where the collective result set would be a multiplication product of each element of one table with each element of another table. So, for example, if you have two tables with 10 rows each, the CROSS join would return 100 rows as its result set. Imagine tables with 1000’s of rows. This is reason one has to be very careful before performing a Cross Join. One good point to note, Cross Join is not the same as full Outer Join.

Syntax:

SELECT <column name(s)>
FROM < table_name1 > CROSS JOIN < table_name2 >

Also the following is valid, note that the key word CROSS JOIN is not used.

SELECT <column name(s)>
FROM < table_name1 >, < table_name2 >

Self-Join:- As the name suggests, a self-join combines the information from two or more rows of the “same” table into a single row, effectively joining the table with itself. The usage arises when same column value would be used for comparison. For example, compare sales figure of a sales person with that of another sales person with different designation.

Following query can be used.

SELECT  a.name,
        a.dept_no,
        a.sales_amt,
        b.name,
        b.dept_no,
        b.sales_amt
FROM sales a, sales b
WHERE a.dept_no = b.dept_no
AND a.desig IN (’Sales Executive’, ’Sales Supervisor’)
AND a.sales_amt <> b.sales_amt;

This particular query would treat the one “sales” tables to two fictitious “sales” table and give the desired result which is done be aliasing the “sales”  table twice once as a and again as b. This is also called as correlation names / range variables in ANSI. Remember to put the alias names before each column to be selected.

4 thoughts on “Inner Joins in Teradata.

  1. In first two statements, don’t you miss something like:
    … (INNER) JOIN ON (join conditions like table 1.column = table 2.column) ?
    as far as I know this is the recommended ANSI syntax… Don’t you need that in Teradata as well?

  2. Example 3 is more like Oracle-type syntax. When doing left joins, are you allowed to use (+) operator like in Oracle or do you need to write:
    LEFT (OUTER) JOIN ON (join conditions) ?

  3. You are right Rastislav, the above syntax was incomplete one. ON clause is mandatory in case you are using the keyword JOIN.
    For your 2nd point, I guess the plus(+) operator would not work in Teradata as it is part of Oracle’s PL/SQL.

    Thank you for valuable comments..

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>