Database Glossary – J
Useful Terms For Working With Data
A relational operator (query) that combines data from multiple tables into a single result table. Tables must have at least one field (sometimes called the join or linking field) in common, so that values from corresponding records in each table are matched up correctly.
Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.
Join, Full Outer
The full outer join combines the functions of both the left outer join and the right outer join retaining the unmatched record from both. Any time a record has no match in the other table, the selected fields from the other table contain null values. Records resulting from a match between the tables, contain data values from both base tables.
An inner join discards all records from the result table that don’t have corresponding records in both source tables, while an outer join preserves unmatched records.
Join, Left Outer
The left outer join preserves unmatched records from the left table but discards unmatched records from the right table. (The left table is the one that precedes the keyword JOIN and the right table is the one that follows it.) The resulting set of a left outer join includes all the records from the left table, not just the ones in which the joined columns match, so that when a record in the left table has no matching counterpart in the right table, it contains null values for columns coming from the right table.
Outer joins can be a left, right, or full.
Join, Right Outer
A right outer join is the reverse of a left outer join. The right outer join preserves unmatched records from the right table but discards unmatched records from the left table. (The left table is the one that precedes the keyword JOIN and the right table is the one that follows it.) All records from the right table are returned. Null values are returned for the left table any time a right table record has no matching counterpart in the left table.