SQL Join – Inner, Left, Right & Full Joins

We have large units of data stored in our database, consisting of thousands of tables. When we need to look into a specific set of data satisfying various conditions, we face problems while combining a large amount of data.

The data is spread into multiple tables, and thus to extract the data from multiple tables we use various joins to convert the tables into one table.

Stay updated with latest technology trends
Join DataFlair on Telegram!!

What are Joins in SQL and Use of SQL Joins?

SQL join statements help us to combine the rows and columns and parts of various tables and view them as one. By the use of joins, we can easily combine and present data into a single table.

This makes it easy for us to perform queries and transactions on the data needed by us. We perform joins based on one or more common fields in the two or more tables.

Let us try to understand by using the example of a school student’s data.

We have a table storing the data of the student with all the personal details including the parent’s details and health details of the student. On the other side, we have a table that stores the data on the performance of the student.

Now suppose we want to run a survey on our school students which requires the data of the parents and the performance of the student over time. To do any sort of analysis, we require data to be present in the same table.

To obtain this we use joins on the tables. By using join we can combine data that satisfies various conditions and logics we specify and require.

When we use joins, they are done on multiple parameters and conditions passed. Here we will discover more about the types and uses of the available joins in SQL.

Demo Database

Let us view our tables, i.e. DataFlair_emp1 and DataFlair_emp2.
Query: 

SELECT * FROM DataFlair_emp1 ;

view table DataFlair_emp1

Query: 

SELECT * FROM DataFlair_emp2 ;

View table DataFlair_emp2

Types of SQL Joins

We have four types of joins in SQL, and they are as follows:

Sr.NoJOINDescription
1Inner JoinFetches value common in all the tables.
2Left (Outer) JoinFetches all values from the left table and matching records from the right table.
3Right (Outer) JoinFetches all values from the right table and matching records from the left table.
4Full (Outer) JoinFetches value when there is a match in the right or left table.

Below are the diagrammatic representations of all the SQL Joins:


SQL Inner Join

SQL Left Join

SQL Right Join

SQL Full join

SQL Inner Join Syntax

SELECT * 
FROM tableName1
INNER JOIN tableName2
ON tableName1.matchingColumn = tableName2.matchingColumn ;

Example 1: [Inner Join] Let us view all the data in both of our tables using the inner join.
Query:

SELECT *
FROM DataFlair_emp1
INNER JOIN DataFlair_emp2
ON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;

SQL inner join example

SQL Left (Outer) Join Syntax

SELECT * 
FROM tableName1
LEFT JOIN tableName2
ON tableName1.matchingColumn = tableName2.matchingColumn ;

Example 2: [Left (Outer) Join] Let us use the left join on both of our tables.
Query:

SELECT *
FROM DataFlair_emp1
LEFT JOIN DataFlair_emp2
ON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;

SQL left join Example

SQL Right (Outer) Join Syntax

SELECT * 
FROM tableName1
RIGHT JOIN tableName2
ON tableName1.matchingColumn = tableName2.matchingColumn ;

Example 3: [Right (Outer) Join] Let us use the right join on both of our tables.
Query:

SELECT *
FROM DataFlair_emp1
RIGHT JOIN DataFlair_emp2
ON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;

SQL Right Join Example

SQL Full (Outer) Join Syntax

SELECT * 
FROM tableName1
RIGHT JOIN tableName2
ON tableName1.matchingColumn = tableName2.matchingColumn ;

Example 4: [Full (Outer) Join] Let us use the full join on both of our tables.
Query:

SELECT *
FROM DataFlair_emp1
FULL JOIN DataFlair_emp2
ON dataflair_emp1.emp_id = dataflair_emp2.emp_id ;

SQL full outer join Example

Summary

When we have to deal with large volumes of data, we need to do a lot of modifications and tweaks in our data to extract some meaningful knowledge.

With the help of joins, we are able to easily combine the data and draw useful insights out of it. We have discussed four significant types of joins, i.e. Full outer join, Left outer join, Right outer join, and inner join.

Did you know we work 24x7 to provide you best tutorials
Please encourage us - write a review on Google | Facebook

2 Responses

  1. Rudrakshi says:

    To display easy words to understand

  2. Roberto Pizano says:

    Do you allow to print your content, and how much will the fee. (I am a student).

Leave a Reply

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

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.