SQL Joins

最新推荐文章于 2024-11-20 23:21:33 发布

原创最新推荐文章于 2024-11-20 23:21:33 发布 · 153 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

本文详细介绍了SQL中的各种连接类型，包括等值连接、自连接、笛卡尔积、内连接、外连接（左外连接、右外连接、全外连接）、反连接及半连接等，并解释了它们的工作原理和应用场景。

Joins

---------------------------------extracted form ref

Equijoins: Combines rows that have equivalent values for the specified columns.

Self Joins: A join of a table to itself.

Cartesian Products: If no join condition, returns Cartesian product. Combines each row of one table with each row of the other.

Innerjoins: (or named Simple Join) A join of two or more tables that returns only those rows that satisfy the join condition.

Outerjoins: Extends the result of a simple join. Retuns all rows that satisfy the join condition and also returns some or all of those rows form one table for which no rows from the other satisfy the join condition. Left Outer Join: If performs an outer join of table A and table B and returns all rows from A use the LEFT[OUTER] JOIN sytax in the FROM clause, or apply the outer join perator(+) to all columns of B in the join condition in the WHERE clause. Right Outer Join acts can be deduced like left outer join. Full Outer Join: Returns all rows form A and B, extended with nulls if they do not satisfy the join condition, use the FULL[OUTER] JOIN syntax in the FROM clause.

Antijoins: Returns rows form the left side of the predicate for which there are no corresponding rows on the right side of the redicate. That is rows that match in A , not in B(suppose A is the left side).

Semijoins: Returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when mutiple rows on the right side satisfy the criteria of the subquery.