My OperaJOIN Algorithms in Teradata SQL
Friday, July 4, 2008 1:49:17 AM
Credit: ReadVitamin
In a JOIN processing, columns from two or more tables are simulated into a virtual table. In Teradata, we can have as many as 64 table JOINs per query, but in case if you need to JOIN these many tables, you better take good advice from your DBA, to avoid, facing last minute music. Also a JOIN does not have to be with two or more tables, we can also JOIN one single table with itself, which is called “Self Join” where the information from two or more rows of the same table put into a single result row.
There are varieties of JOINS, which are different by the “conditions” specified in the SQL query. We will discuss on the following types:
• Natural or Equijoin
• Inner
• Outer
Natural or Equijoin
Because Teradata SQL does not support the ANSI SQL-2003 NATURAL JOIN syntax, it treats natural and equijoins as one and the same.
The natural join is an equality join (with = sign) made over a common column set with matching column names such as a primary key-foreign key relationship that is expressed in a WHERE clause. For example,
WHERE A.empnum = B.empnum
While both Natural as well as Equijoin are made over an equality condition (with = sign), the column names on which tables are joined need not match in an equijoin (but data should), while they must match in a natural join. For example, in the WHERE condition above, if there is a column present in table B with matching data and the name is emp_no then we can rewrite the query as follows:
WHERE A.empnum = B.emp_no
Inner Joins: The inner join, most of the time referred as just JOIN, combines only the rows that have specific similarity between the joined tables (employee number in the above example). One good thing about using INNER JOINS is that one can avoid unwanted cross joins. This happens by mistake. Following types of INNER JOINS are used in SQL.
Ordinary Inner Join
Cross Join
Self-Join
Outer Joins: The outer join is an extension to inner join that returns not only common rows between the tables, but also it returns rows that do NOT have anything in common. This non-matching row might be due to a NULL value or invalid data. Depending on how you code, outer join can return the inner join rows plus any of the no matching rows from the:
Left Table (Left Outer Join).
Right Table (Right Outer Join).
Both Tables (Full Outer Join).
In a JOIN processing, columns from two or more tables are simulated into a virtual table. In Teradata, we can have as many as 64 table JOINs per query, but in case if you need to JOIN these many tables, you better take good advice from your DBA, to avoid, facing last minute music. Also a JOIN does not have to be with two or more tables, we can also JOIN one single table with itself, which is called “Self Join” where the information from two or more rows of the same table put into a single result row.
There are varieties of JOINS, which are different by the “conditions” specified in the SQL query. We will discuss on the following types:
• Natural or Equijoin
• Inner
• Outer
Natural or Equijoin
Because Teradata SQL does not support the ANSI SQL-2003 NATURAL JOIN syntax, it treats natural and equijoins as one and the same.
The natural join is an equality join (with = sign) made over a common column set with matching column names such as a primary key-foreign key relationship that is expressed in a WHERE clause. For example,
WHERE A.empnum = B.empnum
While both Natural as well as Equijoin are made over an equality condition (with = sign), the column names on which tables are joined need not match in an equijoin (but data should), while they must match in a natural join. For example, in the WHERE condition above, if there is a column present in table B with matching data and the name is emp_no then we can rewrite the query as follows:
WHERE A.empnum = B.emp_no
Inner Joins: The inner join, most of the time referred as just JOIN, combines only the rows that have specific similarity between the joined tables (employee number in the above example). One good thing about using INNER JOINS is that one can avoid unwanted cross joins. This happens by mistake. Following types of INNER JOINS are used in SQL.
Ordinary Inner Join
Cross Join
Self-Join
Outer Joins: The outer join is an extension to inner join that returns not only common rows between the tables, but also it returns rows that do NOT have anything in common. This non-matching row might be due to a NULL value or invalid data. Depending on how you code, outer join can return the inner join rows plus any of the no matching rows from the:
Left Table (Left Outer Join).
Right Table (Right Outer Join).
Both Tables (Full Outer Join).
Anonymous # Friday, February 19, 2010 12:29:21 AM
Anonymous # Monday, March 8, 2010 8:56:18 AM