In this article we will discuss and learn about different types of Joins in SQL. SQL join combines data of two or more tables.It joins common field between two or more tables to fetch records.
Different types of SQL Joins are given below:
- Inner Join
- Left Join
- Right Join
- Full outer join
Let’s have a detailed description of each one of them:
- INNER JOIN:
- It selects only those rows from join tables which satisfies the join condition.
- Now in above figure you can see that in Phone_master table country code 91 is for India and this India has one record in Contact_Detail table.
- You can see in the Contact_Detial table country code 91 has two phone numbers 9988825159, 9988823182.
- It means in Contact_Detail the country code 91 corresponds to India in phone master table.
- Also, you can see for Pakistan we don’t have entry in Contact_Detail table and in the same way for contact id 15 we don’t have a country code in Phone_master table.
- Now if we make an inner join, wherever the data is matched between two data tables only those rows will be displayed.
- LEFT JOIN:
- It selects all rows from the Phone_master table along with the matching rows in the Contact_Detail table. It sets Null as result in the Contact_Detail table, if the Contact_Detail table does not have matching row
- Now, let’s consider the data in following tables:
- Now if I make left join then all the data from the left table i.e (Phone_master) will be displayed. But only data which are having a matching entry of the right table i.e (contact_detail) will be displayed.
- RIGHT JOIN:
- It select’s all rows from Contact_Detail table along with matching rows from Phone_master table.
- It set’s Null as a result in Phone_master table, if Phone_master table does not have matching row
- Now consider the data in following table:
- Now if I make right join then all the data from Contact_Detail table will be displayed.
- But only data which are having a matching entry of the left table (phone_master) will be displayed.
- FULL OUTER JOIN:
- It select’s all rows from Phone_master and Contact_Detail table.
- If no rows match on both table than it set’s null value as a result for that table column
- Now consider the following table:
Example: For example consider the data in following tables:
Query :
select * from Phone_Master inner join Contact_Detail on Phone_Master.Country_code =Contact_Detail.Country_code
Output 1 :
- You can see an inner join display only matching rows from both tables excluding Pakistan and contact id 15
Query :
select * from Phone_Master left join Contact_Detail on Phone_Master.Country_code =Contact_Detail.Country_code
Output 2:
- You can see, it display’s all the records from Phone_master table but from Contact_Detial it only display’s matching record’s.
Query :
select * from Phone_Master right join Contact_Detail on Phone_Master.Country_code =Contact_Detail.Country_code
Output 3:
- You can see it display’s all the records from Contact_Detail table but from Phone_master table it only display’s matching records excluding Pakistan from Phone_master table because there is no matching record in Contact_Detial table with country code 92
select * from Phone_Master full outer join Contact_Detail on Phone_Master.Country_code =Contact_Detail.Country_code
Output 4 :
- It is basically the union of both the tables as you can see its populating the matched as well as non- matched record.
Hence, we have learnt about different types of joins in SQL and implemented them along with examples.