Original Join Syntax Teradata

The SQL join is a traditional and powerful tool in a relational database. The first difference between a join and a single table SELECT is that multiple tables are listed using the FROM clause. The first technique, shown below, uses a comma between the table names. This is the same technique used when listing multiple columns in the SELECT, ORDER BY or most other area that allows for the identification of more than one object.

The following is the original join syntax for a two-table join:

The following tables will be used to demonstrate the join syntax:

tables will be used to demonstrate the join syntax

tables will be used to demonstrate the join syntax

The common domain between these two tables is the customer number. It is used in the WHERE clause with the equal condition to find all the rows from both tables with matching values. Since the column has exactly the same name in both tables, it becomes mandatory to qualify this column's name so that the PE knows which table to reference for the data. Every appearance of the customer number in the SELECT must be qualified.

The next SELECT finds all of the orders for each customer and shows the Customer's name, Order number and Order total using a join:

5 Rows Returnedfinds all of the orders for each customer and shows the Customer's name

In the above output, all of the customers, except one, have a single order on file. However, Billy's Best Choice has placed two orders and is displayed twice, once for each order. Notice that the Customer number in the SELECT list is qualified and returned from the Customer table. Does it matter, in this join which table is used to obtain the value for the Customer number?

Your answer should be no. This is because the value in the two tables is checked for equal in the WHERE clause of the join. Therefore, the value is the same regardless of which table is used. However, as mentioned earlier, you must use the table name to qualify any column name that exists in more than one table with the same name. Teradata will not assume which column to use.

The following shows the syntax for a three-table join:

The next three tables are used to demonstrate a three-table join:

demonstrate a three-table join

Students table

Students table

The first two tables represent the students and courses they can attend. Since a student can take more than one class, the third table Student_Course is used to associate the two main tables. It allows for one student to take many classes and one class to be taken by many students (a many-to-many relationship).

The following SELECT joins these three tables on the common domain columns to find all courses being taken by the students:

13 Rows Returnedcourses being taken by the students
courses being taken by the students

It is required to have one less equality test in the WHERE than the number of tables being joined. Here there are three tables and two equalities on common domain columns in the tables. If the maximum of 64 tables is used, this means that there must be 63 comparisons with 63 AND logical operators. If one comparison is forgotten, the result is not a syntax error; it is a Cartesian product join.

Many times the request adds some residual conditions to further refine the output. For instance, the need might be to see all the students that have taken the V2R3 SQL class. This is very common since most tables will have thousands or millions of rows. A way is needed to limit the rows returned. The residual conditions also appear in the WHERE clause.

In the next join, the WHERE of the previous SELECT has been modified to add an additional comparison for the course:

3 Rows Returnedcourses being taken by the students

The added residual condition does not replace the join conditions. Instead it adds a third condition for the course. If one of the join conditions is omitted, the result is a Cartesian product join.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics