- FROM Table1 INNER JOIN Table2 ON Table1.Field=Table2.Field
Introduction
Joins, in SQL, are a way of linking Recordsets together.
Usually, they involve restricting which data is returned in the output Recordset, although this is not true for Outer Joins (discussed later). Conceptually, a JOIN is applied before any WHERE clause which may be specified.
When Recordsets are JOINed they typically produce more records in the output Recordset than there are in the input Recordsets. This is not always true though.
JOINed Recordsets convert two input Recordsets into a single output Recordset which contains the fields of both of the input Recordsets.
In SQL, tables can be joined in various ways.
- INNER JOIN
In its simplest form, this can be :
This produces a record in the output Recordset for each combination of every record in one Recordset with every record in the other Recordset... where the ON statement is TRUE. If there is no matching record then it will not be included in the output Recordset. If there is more than one then all combinations will be included in the output Recordset.
See the examples below to get a better understanding of this.
- LEFT JOIN; RIGHT JOIN
In its simplest form, this can be :- FROM Table1 LEFT JOIN Table2 ON Table1.Field=Table2.Field
This produces a record in the output Recordset for each combination of every record in one Recordset with every record in the other Recordset... where the ON statement is TRUE... OR the second (for a LEFT JOIN) or first (for a RIGHT JOIN) record doesn't exist. This sets one of the input Recordsets as a higher priority than the other. It includes all records of one Recordset but only those of the other Recordset that are matched. If there is more than one matching record then all combinations will still be included in the output Recordset.
See the examples below to get a better understanding of this.
- Outer Join
Notice this does not conform to the format of the others.
In SQL, this is specified by simply listing the two tables separated by a comma (,).
The way an Outer join is processed is that every combination of all the records in both input Recordsets is returned as a record in the output Recordset. This can produce a large number of records from relatively small input Recordsets.
See the examples below to get a better understanding of this.
Examples
- Table1 Table2
- Name1 Value1 Name2 Value2
- Andy 11 Andy 101
- Andy 12 Andy 102
- Bob 21 Charlie 301
- Bob 22 Charlie 302
- Don 41 Don 401
INNER JOIN Example
- SELECT Name1,Value1,Name2,Value2
- FROM Table1 INNER JOIN Table2
- ON Table1.Name1=table2.Name2
Output Recordset
- Name1 Value1 Name2 Value2
- Andy 11 Andy 101
- Andy 11 Andy 102
- Andy 12 Andy 101
- Andy 12 Andy 102
- Don 41 Don 401
LEFT JOIN Example
- SELECT Name1,Value1,Name2,Value2
- FROM Table1 LEFT JOIN Table2
- ON Table1.Name1=table2.Name2
Output Recordset
- Name1 Value1 Name2 Value2
- Andy 11 Andy 101
- Andy 11 Andy 102
- Andy 12 Andy 101
- Andy 12 Andy 102
- Bob 21 Null Null
- Bob 22 Null Null
- Don 41 Don 401
RIGHT JOIN Example
- SELECT Name1,Value1,Name2,Value2
- FROM Table1 RIGHT JOIN Table2
- ON Table1.Name1=table2.Name2
Output Recordset
- Name1 Value1 Name2 Value2
- Andy 11 Andy 101
- Andy 11 Andy 102
- Andy 12 Andy 101
- Andy 12 Andy 102
- Null Null Charlie 301
- Null Null Charlie 302
- Don 41 Don 401
Outer Join Example
- SELECT Name1,Value1,Name2,Value2
- FROM Table1,Table2
Output Recordset
- Name1 Value1 Name2 Value2
- Andy 11 Andy 101
- Andy 11 Andy 102
- Andy 11 Charlie 301
- Andy 11 Charlie 302
- Andy 11 Don 401
- Andy 12 Andy 101
- Andy 12 Andy 102
- Andy 12 Charlie 301
- Andy 12 Charlie 302
- Andy 12 Don 401
- Bob 21 Andy 101
- Bob 21 Andy 102
- Bob 21 Charlie 301
- Bob 21 Charlie 302
- Bob 21 Don 401
- Bob 22 Andy 101
- Bob 22 Andy 102
- Bob 22 Charlie 301
- Bob 22 Charlie 302
- Bob 22 Don 401
- Don 41 Andy 101
- Don 41 Andy 102
- Don 41 Charlie 301
- Don 41 Charlie 302
- Don 41 Don 401
Every possible combination is included.
Source: thescripts.com
