How do JOINs work in SQL? Three or more than three tables join In SQL
Join in SQL
A Join is used to combine row from two or more table .based on min one common column. there is various type of join.
- Inner join
- Left join
- Right join
- Full outer join
- Self-join
- Inner Join: – The most commonly used for the join is inner join .they also called Equijoin. Two tables combine with one condition (one row’s value match in both tables).
The syntax of an inner join:-
[ Select tablename.columnname(s) from table1 Inner join table2 on table1.columnname =table2.columnname ]
Ex: – we have two table first table names is a category and second is subcategory and the common row is category so let see how to use inner join in both tables.
(Category)
(subcategory)
(Output)
- Left Join: – In the left join returns all record in the left table but only match record selected in the right table, unmatched record value null.
The syntax of a left join:-
[ Select tablename.columnname(s) from table1 Left join table2 on table1.columnname =table2.columnname ]
Ex:- we have two table first table names is category and second is subcategory and the common row is category id so let see how to use Left join in both tables.
(category)
(subcategory)
(Output)
- Right, Join: – In the right join returns all record in right table but only match record selected in the left table, unmatched record value null.
The syntax of right join:-
[ Select tablename.columnname(s) from table1 Right join table2 on table1.columnname =table2.columnname ]
Ex: – we have two table first table names is a category and second is subcategory and the common row is category so let see how to use Right join in both tables.
(category)
(subcategory)
(Output)
- Full Outer Join: – this keyword returns all matching record in both tables .it put a null value on the place of the match not found. It can potentially return very large datasets.
The syntax of Full outer join:-
[ Select tablename.columnname(s) from table1 Full Outer join table2 on table1.columnname =table2.columnname ]
- Self Join: – Self Join is used for comparing itself.
The syntax of self-join:-
[ Select tablename.columnname(s) from table1 Self join table2 on table1.columnname =table2.columnname ]
Join in three tables
We have three table first table category second is subcategory and third is a product
The syntax of three table join:-
[ Select tablename.columnname(s) from table1 Inner join table2 on table1.columnname =table2.columnname Inner join table 3 on table2.columnname = table 3.column name ]
Ex:-
(category)
(subcategory)
(product)
(Output)
Shubham Jain administrator
4 Comments so far
flash onesie for adultsPosted on2:09 am - Sep 20, 2018
It’s difficult to find knowledgeable people in this particular topic, but you sound like you know what you’re talking about! Thanks
oprolevorterPosted on4:45 pm - Sep 28, 2019
I am happy that I noticed this web blog, just the right info that I was looking for! .
Stuart GramboPosted on12:55 am - May 5, 2020
“This website really has all of the info I wanted about this subject and didn’t know who to ask.”
Nigel BizarroPosted on10:41 pm - May 26, 2020
“Simply wish to say your article is as astounding. The clarityin your post is simply spectacular and i can assume you’re an expert on this subject.Well with your permission let me to grab your RSS feed to keep up to date with forthcoming post.Thanks a million and please carry on the gratifying work.”
About the author