Joins in SQL

Joins

A join is 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:

 

  1. 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:

  1. 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:

 

  1. 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:

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *