A join combines records from two or more tables in a relational database. In the Structured Query Language (SQL), there are two types of joins: "inner" and "outer". Outer joins are subdivided further into left outer joins, right outer joins, and full outer joins.
Inner join
This is the default join method if nothing else is specified. An inner join essentially finds the intersection between the two tables. The join takes all the records from table A and finds the matching record(s) from table B. If no match is found, the record from A is not included in the results. If multiple results are found in B that match the predicate then one row will be returned for each (the values from A will be repeated).
Special care must be taken when joining tables on columns that can be NULL since NULL values will never match each other
Left outer join
A left outer join is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" table (A). This means that if the ON clause matches 0 records in B, a row in the result will still be returned—but with NULL values for each column from B.
Right outer join
A right outer join is much like a left outer join, except that the tables are reversed. Every record from the right side, B, will be returned, and NULL values will be returned for those that have no matching record in A.
Full outer join
Full outer joins are the combination of left and right outer joins. These joins will show records from both tables, and fill in NULLs for missing matches on either side.
Inner join
This is the default join method if nothing else is specified. An inner join essentially finds the intersection between the two tables. The join takes all the records from table A and finds the matching record(s) from table B. If no match is found, the record from A is not included in the results. If multiple results are found in B that match the predicate then one row will be returned for each (the values from A will be repeated).
Special care must be taken when joining tables on columns that can be NULL since NULL values will never match each other
Left outer join
A left outer join is very different from an inner join. Instead of limiting results to those in both tables, it limits results to those in the "left" table (A). This means that if the ON clause matches 0 records in B, a row in the result will still be returned—but with NULL values for each column from B.
Right outer join
A right outer join is much like a left outer join, except that the tables are reversed. Every record from the right side, B, will be returned, and NULL values will be returned for those that have no matching record in A.
Full outer join
Full outer joins are the combination of left and right outer joins. These joins will show records from both tables, and fill in NULLs for missing matches on either side.
No comments:
Post a Comment