The Concept of 'Joins' in SQL Server

Combining data from multiple tables using single select statement is called as a JOIN.
Joins are classified into

• Inner Join.
• Outer Join
• Cross Join

 Inner Join are again classified into:

• Equi Join
• Natural Join
• Non-Equi Join
• Self Join

Outer Join are classified into

• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join

1. Inner Join:  Inner Join is the join that can display only the rows that satisfy the given join condition.
2. Equi Join: The Inner Join that uses equal operator in the join condition is called as Equi Join.
3. Natural Join: An inner join is called as natural join only when it satisfies the following three condition.

• Join is Equi Join.
• All common columns in the table must be in the joined condition.
• Only one set of common columns is displayed in the output.

   4. Non Equi Join: The inner join that uses the operator other than equal in the join condition is called as non-Equi Join.

    5.  Self Join: A join that joins a taable to its self is called as Self Join.

• Outer Join:  Outer Join is a join that can display the rows that does not satisfy the given join condition along with the rows that satisfy the given condition.

1. Left Outer Join: Left outer Join is an Outer Join that can display the rows that does not satisfy the given condition only from left hand side table. To perform Left outer join with ANSI syntax use the keyword Left Outer Join and to perform Left Outer Join with non-ANSI syntax use the keyword *= in the join condition.
2. Right Outer Join: Right outer Join is an Outer Join that can display the rows that does not satisfy the given condition only from right hand side table. To perform right outer join with ANSI syntax use the keyword Left Outer Join and to perform Right Outer Join with non-ANSI syntax use the keyword =* in the join condition.
3. Full Outer Join:  Full outer Join is an Outer Join that can display the rows that does not satisfy the given condition only from both the tables. To perform full outer join with ANSI syntax use the keyword full Outer Join and to perform Full Outer Join with non-ANSI syntax perform union on left outer join and right outer join statements.
4. Cross Outer Join: The join that joins every row in the first table with every row in the second table is called as Cross Join.