32,556
Joins, in SQL, are a way of linking Recordsets together.
They involve restricting which data is returned in the output Recordset. When no join is specified but two Recordsets are, then a cartesian product is produced which specifies no restrictions. Conceptually, a JOIN is applied before any WHERE clause which may be specified.
NB. Full Outer Joins are not supported in Access (Jet) SQL.
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 Access (Jet) 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.Expand|Select|Wrap|Line Numbers- FROM Table1 INNER JOIN Table2 ON Table1.Field=Table2.Field
See the examples below to get a better understanding of this.
- LEFT JOIN; RIGHT JOIN (Outer Joins)
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... 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.Expand|Select|Wrap|Line Numbers- FROM Table1 LEFT JOIN Table2 ON Table1.Field=Table2.Field
See the examples below to get a better understanding of this.
- Full Outer Join (Not supported in Access (Jet) SQL)
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... OR, EITHER the first OR second matching record doesn't exist. This sets both of the input Recordsets as the same priority as the other. It includes all records of both Recordsets. If there is more than one matching record then all combinations will still be included in the output Recordset.Expand|Select|Wrap|Line Numbers- FROM Table1 OUTER JOIN Table2 ON Table1.Field=Table2.Field
See the examples below to get a better understanding of this.
- No Join (Cartesian Product)
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 unjoined links are 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 (Cartesian Product).
See the examples below to get a better understanding of this.
Examples
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- SELECT Name1,Value1,Name2,Value2
- FROM Table1 INNER JOIN Table2
- ON Table1.Name1=table2.Name2
Expand|Select|Wrap|Line Numbers
- 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 OUTER JOIN Example
Expand|Select|Wrap|Line Numbers
- SELECT Name1,Value1,Name2,Value2
- FROM Table1 LEFT JOIN Table2
- ON Table1.Name1=table2.Name2
Expand|Select|Wrap|Line Numbers
- 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 OUTER JOIN Example
Expand|Select|Wrap|Line Numbers
- SELECT Name1,Value1,Name2,Value2
- FROM Table1 RIGHT JOIN Table2
- ON Table1.Name1=table2.Name2
Expand|Select|Wrap|Line Numbers
- 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
FULL OUTER JOIN Example
Expand|Select|Wrap|Line Numbers
- SELECT Name1,Value1,Name2,Value2
- FROM Table1 OUTER JOIN Table2
- ON Table1.Name1=table2.Name2
Expand|Select|Wrap|Line Numbers
- 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
- Null Null Charlie 301
- Null Null Charlie 302
- Don 41 Don 401
NB. This type of Join is not supported in Access (Jet) SQL.
No Join (Cartesian Product) Example
Expand|Select|Wrap|Line Numbers
- SELECT Name1,Value1,Name2,Value2
- FROM Table1,Table2
Expand|Select|Wrap|Line Numbers
- 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
