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.

Our Random Articles

More Links