Ordinary Inner Join
Definitions: Join and Inner Join
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
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:
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 SQL Functions, Operators, Expressions, and Predicates.
If the join predicate is an inequality on a PERIOD value expression, Teradata Database processes 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;