Ordinary Inner Join | SQL Joins | Teradata Vantage - Ordinary Inner Join - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

A join allows you to select columns and rows from two or more tables and views. You can join as many as 128 tables and views per query block.

An inner join projects data from two or more tables or views that meet specific join conditions. Each source must be named and the join condition, the common relationship among the tables or views to be joined, must be specified explicitly in a WHERE clause.

Components of an Inner Join



Define table_A as the row set containing sections 1 and 2. Refer to table_A as left_table.

Define table_B as the row set containing sections 1 and 3. Refer to table_B as right_table.

The inner join of table_A and table_B is section 1, as indicated by the following Venn diagram.



The Default Join

Unless explicitly declared as outer joins, all joins are inner joins by default. You can, however, specify an explicit inner join using the reserved word INNER.

The following SELECT statement has an inner join of the two tables table_a and table_b.

     SELECT ...
     FROM table_a
     INNER JOIN table_b ...

Because the keyword sequence INNER JOIN is optional (but if you specify the keyword INNER, you must immediately follow it with the keyword JOIN), the following SELECT statements are also correct examples of inner joins:

     SELECT ...
     FROM table_a
     JOIN table_b ...
     SELECT ...
     FROM table_a, table_b ...

You can specify the join condition using either the ON clause or the WHERE clause for inner joins. Note that an ON clause is mandatory if the keyword JOIN is specified for an inner join.

Note that you cannot specify a SAMPLE clause in a subquery used as an ON clause predicate.

Example: Default Join

You can determine the department location of employee Marston by joining the employee and department tables on the column in which there are values common to both. In this case, that column is deptno.

     SELECT Loc
     FROM department, employee
     WHERE employee.name = 'Marston A'
     AND employee.deptno = department.deptno;
This query asks two questions:
  • What is the number of the department in the employee file that contains Marston?
  • What is the location code for that department in the department file?

The key to answering both questions is the deptno column, which has the same values in both tables and thus can be used to make the join relationship (the actual name of the column is irrelevant).

The result of this inner join is the following report.

     Loc
     ATL

Joins on Views Containing an Aggregate

You can join views containing an aggregate column.

In the following example, the cust_file table is joined with the cust_prod_sales view (which contains a SUM operation) in order to determine which companies purchased more than $10,000 worth of item 123.

     CREATE VIEW cust_prod_sales (cust_no, pcode, sales) AS
     SELECT cust_no, pcode, SUM(sales)
     FROM sales_hist
     GROUP BY cust_no, pcode;

     SELECT company_name, sales
     FROM cust_prod_sales AS a, cust_file AS b
     WHERE a.cust_no = b.cust_no
     AND   a.pcode = 123
     AND   a.sales > 10000;

Joins on PERIOD Value Expressions

The Optimizer treats an equality operator on a PERIOD value expression in the same way as any other expression for join or access planning. See “Period Value Constructor” in Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211.

If the join predicate is an inequality on a PERIOD value expression, Vantageprocesses it as a Cartesian product just as it would any other inequality predicate. Row hashing based on a PERIOD value expression during row redistribution considers the internal field size and value for hash value computation.

     SELECT *
     FROM employee AS e, project_details AS pd
     ON e.period_of_stay = pd.project_period;