SQL Joins – Inner, Outer, Left Outer, Right Outer. How to Explain?

Consider Following 2 tables for the example for various types of Joins

Customer
CustomerId Name Phone
1 John Doe 609-937-9819
2 Mary Blag 609-937-9818
3 Mark Waugh 609-937-9861
Order
OrderID OrderDate Custid
Order1 03/12/2014 2
Order2 01/12/2014 3
Order3 03/05/2014
  • Inner Join – The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. Syntax:
    SELECT column_name(s)
     FROM table1
     INNER JOIN table2
     ON table1.column_name=table2.column_name;
    
    Or
    SELECT column_name(s)
     FROM table1
     JOIN table2
     ON table1.column_name=table2.column_name;
    

    innerjoin

    SELECT Customer.CustomerId, Customer.Name, Orders.OrderId, Orders.OrderDate 
    FROM Customer 
    INNER JOIN Orders ON Customer.CustomerId = Orders.CustId 
    
    Inner Join Query Result
    CustomerId Name OrderId OrderDate
    2 Mary Blag Order1 2014-03-12 00:00:00.000
    3 Mark Waugh Order2 2014-01-12 00:00:00.000
  • LEFT Outer Join – The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
    Syntax:

    SELECT column_name(s)
                FROM table1
                LEFT JOIN table2
                ON table1.column_name=table2.column_name
    
    Or
    SELECT column_name(s)
                FROM table1
                LEFT OUTER JOIN table2
                ON table1.column_name=table2.column_name
    

    left outer join

    SELECT Customer.CustomerId, Customer.Name, Orders.OrderId, Orders.OrderDate 
    FROM Customer 
    LEFT OUTER JOIN Orders ON Customer.CustomerId = Orders.CustId 
    

     

    Left Outer Join Query Result
    CustomerId Name OrderId OrderDate
    1 John Doe NULL NULL
    2 Mary Blag Order1 2014-03-12 00:00:00.000
    3 Mark Waugh Order2 2014-01-12 00:00:00.000
  • RIGHT Outer Join – The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
    Syntax:

    SELECT column_name(s)
                FROM table1
                RIGHT JOIN table2
                ON table1.column_name=table2.column_name
    
    Or
    SELECT column_name(s)
                FROM table1
                RIGHT OUTER JOIN table2
                ON table1.column_name=table2.column_name
    

    right outer join

    SELECT Customer.CustomerId, Customer.Name, Orders.OrderId, Orders.OrderDate 
    FROM Customer 
    RIGHT OUTER JOIN Orders ON Customer.CustomerId = Orders.CustId 
    
    Right Outer Join Query Result
    CustomerId Name OrderId OrderDate
    2 Mary Blag Order1 2014-03-12 00:00:00.000
    3 Mark Waugh Order2 2014-01-12 00:00:00.000
    NULL NULL Order3 2014-03-05 00:00:00.000
  • FULL Outer Join – The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
    Syntax:

    SELECT column_name(s)
                FROM table1
                FULL OUTER JOIN table2
                ON table1.column_name=table2.column_name
    

    full outer join

    SELECT Customer.CustomerId, Customer.Name, Orders.OrderId, Orders.OrderDate 
    FROM Customer 
    FULL OUTER JOIN Orders ON Customer.CustomerId = Orders.CustId 
    
    Full Outer Join Query Result
    CustomerId Name OrderId OrderDate
    1 John Doe NULL NULL
    2 Mary Blag Order1 2014-03-12 00:00:00.000
    3 Mark Waugh Order2 2014-01-12 00:00:00.000
    NULL NULL Order3 2014-03-05 00:00:00.000
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s