How do JOINs work in SQL? Three or more than three tables join In SQL

  • Home   /  Uncategorized   /  
  • How do JOINs work in SQL? Three or more than three tables join In SQL
ByShubham Jain

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)

                                                                                                                                                          

 

  [ select category.category id, category.category name ,subcategory.subcategory name FROM category INNER JOIN    subcategory ON category.category id =subcategory.category id  ]

 

(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)

  [ select category.category id, category.category name ,subcategory.subcategory name FROM category Left JOIN    subcategory ON category.category id =subcategory.category id  ]

(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)

                                                                                                            

  [ select category.category id, category.category name ,subcategory.subcategory name FROM category Right JOIN    subcategory ON category.category id =subcategory.category id  ]

(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)

   [select category.category id, category.category name,subcategory.subcategory id ,subcategory.subcategory name ,product.product name FROM category Inner JOIN    subcategory ON category.category id =subcategory.category id Inner join product on subcategory.subcategory id = product.subcategory id ]

 

(Output)

 

 

About the author

Shubham Jain administrator

1 Comment so far

Reply

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

Leave a Reply

Code Krypt @ 2018