SQL SERVER – Introduction to JOINs – Basic of JOINs

There are four types of Joins in SQL-SERVER:
1. Inner Join.

2. Outer Join

a. Left Outer Join.

b. Right Outer Join.

c. Full Outer Join.

3. Cross Join.

4. Self Join.

Inner Join

Inner join returns only those records/rows that match/exists in both the tables

Outer Join

We have three types of Outer Join.

            Left Outer Join

Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

Right Outer Join

Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values.

Full Outer Join

Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables. If there are no columns matching in the both tables, it returns NULL values.

Cross Join

Cross join is a Cartesian join means Cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. 

Self Join

Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins: Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to it. Hence Self join is not a type of Sql join.