Joins in SQL are used to combine two or more tables. It returns the matching rows from the combined tables by using reference column in both the tables.

Types of Joins:

1. Inner Join:

Inner join or Equi-join, returns rows from the both the tables based on the matching values of the columns.

Example:

Below we have two tables

 

In above tables, Student_ID and ID columns have matching values.

We can join Table 1 with Table 2 using Inner Join by referencing

Table 1.Student_ID = Table 2.ID

 SQL Query:

SELECT *  FROM Table 1

INNER JOIN Table 2 ON Table 1.Student_ID = Table 2.ID

Result:

 

2. Left Outer Join:

Returns all the rows from the left table and matching rows from the right table and remaining unmatched rows in the right table shown as ‘NULL’ Values.

Example:

Below we have two tables

 

Now we join the Table 1 with Table 2 using Left  Join by referencing Table 1.Student_ID = Table 2.ID

SQL Query:

SELECT * FROM Table 1 LEFT JOIN Table 2 ON Table 1.Student_ID = Table 2.ID

Result:

3. Right Outer Join:

Returns all the rows from the Right table and matched rows from the left table and remaining unmatched rows in the left table shown as ‘NULL’ Values.

Example:

Below we have two tables

 

Now we join the Table 1 with Table 2 using Right Join by referencing Table 1.Student_ID = Table 2.ID

SQL Query:

SELECT * FROM Table 1 RIGHT JOIN Table 2 ON Table 1.Student_ID = Table 2.ID

Result:

 

4. Full Outer Join:

It is a Union of both Left outer join and Right outer join.

Example:

Below we have two tables

 

Now we join the Table 1 with Table 2 using Full Outer Join by referencing Table 1.Student_ID = Table 2.ID

 SQL Query:

SELECT * FROM Table 1 FULL OUTER JOIN Table 2 ON Table 1.Student_ID = Table 2.ID

Result: