SQL Joins

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.