Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Joins and it's Uses

    • 0
    • 1
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 192
    Comment on it

    In SQL **joins** are used when we want to select data and information from two or more tables on the basis of some relationships between one or more columns in tables. SQL Joins are used to relate information in different tables. Thus,we can say that SQL Join is used in SQL query to retrieve rows from two or more tables on the basis of some relationship between columns of both the tables. A SQL Join can be used in the SQL WHERE Clause of any SQL statement like select, update, delete statements.

    There are different Types of Joins available in SQL :

    1. Inner Joins
    2. Cross Joins
    3. Outer Joins
    4. Self Joins

    Lets understand it with practical example using following tables :

    1. Create a table UserDetails as given below with UserID as Primary key.
     UserID    UserName    FirstName   LastName 
       1      NishaSharma    Nisha    Sharma
       2      RajKaushik      Raj     Kaushik
       3      DivyaDutta      Divya    Dutta
    
    1. Create a table OrderDetails as given below where UserID as Foreign key.

    OrderID     OrderNo    UserID 
     1           543224      1
     2           213424      2
     3           977776      3
    

    Inner Joins : Inner join statement returns all the rows in the resulting table which satisfied the condition given in where clause.

    Example

                SELECT u.UserName ,o.OrderNo 
                  FROM UserDetails u
                   INNER JOIN OrderDetails o
                   ON u.UserID=o.UserID
    

    Resulting table for above SQL Query will be as below :

       UserName    OrderNo 
        NishaSharma    543224
        RajKaushik    213424
        DivyaDutta    977776
    

    Cross Join : A cross join produces Cartesian product of the two or more tables as the resulting table. The number of rows in the resulting table with Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.

    Example

                  SELECT *  FROM UserDetails 
                    CROSS JOIN  OrderDetails
                   Or 
    SELECT * FROM UserDetails, OrderDetails

    Resulting table for above SQL Query will be as below :

     
    UserID UserName      FirstName   LastName    OrderID   OrderNo  UserID 
     1      NishaSharma     Nisha       Sharma        1      543224    1
     1      NishaSharma     Nisha       Sharma        2      213424    2
     1      NishaSharma     Nisha       Sharma        3      977776    3
     2      RajKaushik      Raj         Kaushik       1      543224    1
     2      RajKaushik      Raj         Kaushik       2      213424    2
     2      RajKaushik      Raj         Kaushik       3      977776    3
     3      DivyaDutta      Divya       Dutta         1      543224    1
     3      DivyaDutta      Divya       Dutta         2      213424    2
     3      DivyaDutta      Divya       Dutta         3      977776    3
    

    Outer Joins : This sql join condition returns a resulting table which contains all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition.

    1. Left Outer Join : The left outer join displays all the rows from the Left table and matched rows from the Right table. Syntax

                         SELECT  ColumnNames FROM tablename1 t1 
                          LEFT OUTER JOIN  tablename2 t2 
                          ON  t1.columnname=t2.columnname
    

    Example

                       SELECT   u.UserID,u.UserName,o.OrderNo
                        FROM UserDetails u
                        LEFT OUTER JOIN   OrderDetails o
                         ON   u.UserID=o.UserID
    

    Resulting table for above SQL Query will be as below :

     UserID    UserName    OrderNo 
          1    NishaSharma 543224
          2    RajKaushik  213424
          3    DivyaDutta  977776
    

    2. Right Outer Join : The right outer join displays all the rows from the right side table and matched rows from the left table.

    Syntax

                SELECT  ColumnNames
                FROM  tablename1  t1 
                RIGHT OUTER JOIN  tablename2 t2 
             ON   t1.columnname=t2.columnname 
    

    Example

                   SELECT  u.UserID,u.UserName,o.OrderNo
                   FROM  UserDetails u
                   RIGHT OUTER JOIN OrderDetails o
                   ON  u.UserID=o.UserID 
    

    3. Full Outer Join : Full Outer Join displays all the rows of both the tables which satisfies the join condition in where clause as well as the rows which do not satisfies the join condition.

    Syntax

                       SELECT ColumnList 
                       FROM  tablename1 t1 
                       FULL OUTER JOIN  tablename2 t2 
                       ON  t1.columnname=t2.columnname
    

    Example

                       SELECT   u.UserID,u.UserName,o.OrderNo
                       FROM  UserDetails u
                       FULL OUTER JOIN  OrderDetails o
                       ON  u.UserID=o.UserID
    

    4. Self Join : This Sql join is used to join the table to itself. This join can be use when we want to fetch records from a single table on the basis of some relationship between its columns or when we are looking for records with some unique value. In this scenario any of the above joins can be used to join the table to itself depending on the requirement of the user for resulting table.

 1 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: