Join Processing Chapter 7 Chapter 7 ­ Join Processing Join Processing A join is the combination of two or more tables in the same FROM of a single SELECT statement. When writing a join, the key is to locate a column in both tables that is from a common domain. Like the correlated subquery, joins are normally based on an equal comparison between the join columns. An example of a common domain column might be a customer number. Whether it represents a particular customer, as the primary key, in the Customer table, or the customer that placed a specific order, as a foreign key, in the Order table, it represents the same entity in both tables. Without a common value, a match cannot be made and therefore, no rows can be selected using a join. An Equality join returns matching rows. Any answer set that a subquery can return, a join can also provide. Unlike the subquery, a join lists all of its tables in the same FROM clause of the SELECT. Therefore, columns from multiple tables are available for return to the user. The desired columns are the main factor in deciding whether to use a join or a subquery. If only the columns come from a single table are desired, a subquery or a join work fine. However, if columns from more than one table are needed, a join must be used. In Version 2 Release 3, the number of tables allowed in a single join increased from sixteen (16) to sixty-four (64) tables. Original Join Syntax 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: [<table-name>.]<column-name> [...,<table-name>.<column-name> ] FROM <table-name> [ AS <alias-name> ] ,<table-name> [ AS <alias-name> ] [ WHERE [ [<table-name>.]<column-name>= [<table-name>.]<column-name> ] ] [ ... AND [<table-name>.]<column-name> = [<table-name>.]<column-name> ] [ { ... AND | OR } <column-name = <data-value> ] ; SELECT The following tables will be used to demonstrate the join syntax: The Modern Guide to Teradata SQL Page 133