The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between columns on table. A JOIN is a means for combining fields from two tables by using values common to each.

Kinds of Join:

1.     Inner join

2.     Equi-join

3.     Cross join

4.     Outer joins

a.     Left outer join

b.     Right outer joins

c.      Full outer join

5.     Self-join

Inner Join

An Inner join is the most common join operation used in SQL Server and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The INNER JOIN keyword return rows when there is at least one match in both tables. I have two tables Student Detail and CourseDetail from Inner Join keyword I want to retrieve Name, City and Age from Student Detail table and Course name from CourseDetail table as shown below;

Student Detail table

CourseDetail table

SQL query as shown below to retrieve get data.

Query

SELECT StudentDetail.Name, StudentDetail.City, StudentDetail.Age,CourseDetail.Course

FROM StudentDetail INNER JOIN CourseDetail on StudentDetail.Id=CourseDetail.Id

Result

Equi-Join

 Equi-join returns all the columns from both tables and filters the records satisfying the matching condition as shown below:

Student Detail table

CourseDetail table

Query

SELECT*FROM  StudentDetail  JOIN CourseDetail ON StudentDetail.Id = CourseDetail.Id

Result

Cross Join

Cross join this join has a slightly different format in that it does not have an “ON” clause with a Join Condition. The CROSS join it doesn't need a join condition.  What it does is perform a Cartesian product of the tables involved in the join. This mean every row in the left table is joined to every row in the right table as shown below:

Student Detail table

CourseDetail table

Query

SELECT*FROM  StudentDetail crossJOIN CourseDetail

Result

Outer Joins

Outer join has 3 subcategories. All 3 subcategories have a similar function. This JOINs are basically use for bring 2 tables together but include data even if there the Join Condition is does not find a matching data, it does fill NULL in the table columns.

Left Outer join

The Left outer join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The no matching rows in the second input are returned as NULL values. If no join predicate exists in the Argument column, each row is a matching row as shown below:

Student Detail table

CourseDetail table

Query

SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S LEFT OUTERJOIN CourseDetail C ON S.Id = C.Id

Result

Right Outer Join

The Right join keyword Return all rows from the right table (StudentDetail), even if there are no matches in the left table (CourseDetail) on the Join condition when no record is found in the opposite table NULL values are used for the columns.

Student Detail table

CourseDetail table

Query

SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S Right OUTERJOIN CourseDetail C ON S.Id = C.Id

Result

Full Outer Join

The Full join keyword return rows when there is a match in one of the tables. Full Join is a combination of both Left and Right outer join. All records from both Left table and Right table are in the result set and matched when they can be on the Join condition  when no record is found in the opposite table NULL values are used for the columns as shown below:

Student Detail table

CourseDetail table

Query

SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S Full OUTERJOIN CourseDetail C ON S.Id = C.Id

Result

Self Join

Self join helps in retrieving the records having some relation or similarity with other records in the same table.

Student Detail table

CourseDetail table

Query

SELECT S.ID,S.Name,c.Course,c.Duration FROM StudentDetail S  JOIN CourseDetail C ON S.Id = C.Id

Result

  You can also read these related post-

https://www.mindstick.com/Blog/291/join-in-sql-serverhttps://www.mindstick.com/Articles/35/join-in-sql-server