Joins in Structured Query Language

 

   1. SQL JOIN Query: 

      The Join Query is used to combine rows from two or more column based on related

      column between them. The types of Join  Query are as follows:

        A. INNER JOIN : This Join is used to fetch record that have matching values

                                        in both the table.

      B.  LEFT JOIN: This Join is used to fetch all the details from the left side of table

                                    and matching values from the right side of the table.

      C. RIGHT JOIN: This Join is used to fetch all the details from the right side of the 

                                      table and matching records from the left side of the table

      D. FULL JOIN:  This  Join is used to fetch all the records from both the left and

                                     right side of the table wherever there is matched records .

 

  Let us understand with the help of an Example  :

 Let us assume that there are two tables

Table1: Customers

                 customerId               customername             customercontact            country

                        1                              Alferd                               Maria                     Germany

                        2                               Ana                                   Ana                        Mexico    

                       4                             Antonio                              Antonio                    Mexico

Table2:Orders

                 orderId                       customerid                        orderdate

                        1                                2                                    1996-09-18       

                        2                               37                                   1996-09-19            

                       4                                77                                   1996-09-20        

 

 Syntax: INNER JOIN:   

 Select columnname(s) from tablename1 INNER JOIN tablename2 ON 

 table1.columnname=table2.columnname

Example:

select orders.orderid,customers.customername,orders.Orderdate from orders INNER JOIN customers ON orders.Cutsomerid=customers.customerID  

 

Output:

orderid	customername	Orderdate
10308	Ana	1996-09-18

 

Syntax: LEFT JOIN:

 Select  columnname(s) from table1 LEFT JOIN table2 ON 

 table1.columnname=table2.columnname;

 NOTE: If there is no match freom right side it will return NULL VALUE

               LEFT JOIN is also called as LEFT(OUTER)JOIN

 

Example:

select orders.orderid,customers.customername,orders.Orderdate  

from orders LEFT JOIN customers ON orders.Cutsomerid=customers.customerID

 

Output:

orderid	customername	Orderdate
10308	Ana	1996-09-18
10309	NULL	1996-09-19
10310	NULL	1996-09-20

 

Syntax: RIGHT JOIN

 

 Select columnname(s) from table1 RIGHT JOIN table2 ON 

  table1.columnname=table2.columnname;

  NOTE: If there is no match from right side it will return NULL VALUE

           RIGHT JOIN is also called as RIGHT(OUTER)JOIN

 

Example:

 

select orders.orderid,customers.customername,orders.Orderdate from orders RIGHT JOIN customers ON orders.Cutsomerid=customers.customerID  

 

Output:

 

orderid	customername	Orderdate
10308	Ana	1996-09-18
NULL	Alferd	NULL
NULL	Antonio	NULL

 

 

Syntax: FULL JOIN:

 Select  columnname(s) from table1  FULL JOIN table2 ON 

 table1.columnname=table2.columnname;

Example:

SELECT customers.contactname,orders.Orderdate FROM customers FULL JOIN orders ON customers.customerID=orders.orderid;