Handling table joins is a major challenge to any database engine and using joins is inevitable to any db application. So lets start with the join types
Cross Joins
The Cartesian product of the tables involved. For any 2 table with m and n rows respectively the result is of m*n rows. The result is, each row of table1 concatenated with each and every row of table 2.
Inner Join
The most common type of join where tables are joined based on a predicate(more on the join predicate later).
Equi Join
An instance of the inner join where the predicate is an equality between the columns of the tables.
Natural Join
An inner join where the equality predicate is chosen based on(same) column name in the tables being joined.
Theta Join
Inner join where the predicate is not an equality is a theta join.
Outer Joins
These are the kind of joins where there need not be a matching records for every record of the tables being joined. Outer joins can one of
Left Outer Join
This kind of join between tables say t1 and t2 will always have all records of t1 and only those satisfying the predicate from table t2.The records of t1 that do not have a match in t2 will be appended with the "nulls".
Right Outer Join
This kind of join between tables say t1 and t2 will always have all records of t2 and only those satisfying the predicate from table t1.The records of t2 that do not have a match in t2 will be appended with the "nulls".
Full Outer join
This kind of join between tables say t1 and t2 will always have all records of t1 and t2.The records of t1 or t2 which do not have a satisfying predicate in the other will be appended with nulls.
Self Join
Self join is where a table is joined it self. Why would anybody do that? well..see this to know who is earning what in comparison in the same country
SELECT T1.Name, T1.Salary,T2.Name, T2.Salary From Employees T1, Employees T2 WHERE T1.Country = T2.Country AND T1.Salary < T2.Salary;
Semi Join
A Semi join is same as a inner join except that it will return only one record from the first table even though there are more than one record matching in the second table.
The result of a semi join can be achieved by using the "DISTINCT" clause on the result of a inner join, but the db engine is burdened when it has to apply the "DISTINCT" clause on the large result set.
Semi join may achieved by using "EXISTS" or "INTERSECT" depending on the db engine.
Anti (Semi) Join
An anti join is where the rows of a table that do not have matching record in the other. Anti join can be further classified as "LEFT/RIGHT/FULL" anti joins.
Stay tuned lets us how the join statement is processed.
