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:
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:
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:
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:
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:
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.