Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 449
    Comment on it

    In MySQL join is used to combine records from 2 or more tables based on a common column between them.

    INNER JOIN: It returns all rows when there is a match in both tables.

    LEFT JOIN: It returns all rows from the left table and matched rows from the right table.The result is NULL in the right records for non matched rows.

    RIGHT JOIN: It returns all rows from the right table and the matched rows from the left table.The result is NULL in the left records for non matched rows.

    FULL JOIN: It returns all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.

    We have two tables with the following values.

    user
    
    id  first_name       last_name
    .......................................
    1   John                Simp                 
    2   Chris               Hely                   
    3   Joy                 Roy                      
    4   Jenny               Mill 
    
    
    
    user_address
    
    user_id  Place
    .........................
    1           Canada
    2           Ottawa
    3           London
    5           India
    

    INNER JOIN

    It gives the rows which are common in user and user_address

    syntax is :

    SELECT column_names
    FROM table1
    INNER JOIN table2
    ON table1.common_name = table2.common_name;
    

    Now write the query for above two tables :

    SELECT u.first_name,u.last_name,ua.place
    FROM user u
    INNER JOIN user_address ua
    ON u.id = ua.user_id;
    

    Result:

    first_name      last_name      Place
    ..............................................
    John            Simp           Canada
    Chris           Hely           Ottawa
    Joy             Roy            London
    

    LEFT JOIN

    It will give all selected rows from table user and matched rows form user_address table. It will put NULL for non matched rows in user_address table..

    Syantax

    SELECT column_names
    FROM table1
    LEFT JOIN table2
    ON table1.common_name = table2.common_name;
    

    Now write the query for above two tables :

    SELECT u.first_name,u.last_name,ua.place
    FROM user u
    LEFT JOIN user_address ua
    ON u.id = ua.user_id;
    

    Result:

    first_name      last_name      Place
    ..............................................
    John            Simp           Canada
    Chris           Hely           Ottawa
    Joy             Roy            London
    Jenny           Mill           NULL
    

    RIGHT JOIN

    It will give all the selected rows from table user_address and matched rows form user table. It will put NULL for non matched rows in user table..

    Syantax

    SELECT column_names
    FROM table1
    RIGHT JOIN table2
    ON table1.common_name = table2.common_name;
    

    Now write the query for above two tables :

    SELECT u.first_name,u.last_name,ua.place
    FROM user u
    RIGHT JOIN user_address ua
    ON u.id = ua.user_id;
    

    Result:

    first_name      last_name      Place
    ..............................................
    John            Simp           Canada
    Chris           Hely           Ottawa
    Joy             Roy            London
    NULL            NULL           India
    

    FULL JOIN

    It works same as the UNION operation. It will return all the selected rows from both tables.

    Syantax

    SELECT column_names
    FROM table1
    FULL JOIN table2
    ON table1.common_name = table2.common_name;
    

    Now write the query for above two tables :

    SELECT u.first_name,u.last_name,ua.place
    FROM user u
    FULL JOIN user_address ua
    ON u.id = ua.user_id;
    

    Result:

    first_name      last_name      Place
    ..............................................
    John            Simp           Canada
    Chris           Hely           Ottawa
    Joy             Roy            London
    Jenny           Mill           NULL
    NULL            NULL           India
    

    Hope this will help you :)

 0 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: