Join in SQL

Overview: In this tutorial you will learn about Join in SQL and types of Join and also you will understand how you can use Join in SQL with examples.

Join in SQL

Join in SQL allow you to extract and display data from two and more table into one table based on common field linking with tables. There are always common column/fields which helps to match rows based on common ID and extract the information from another second table to first table.

Important things to know about JOIN in SQL:

• Join in SQL can be perform only between more then two tables
• There should be one common field in two table to perform the JOIN
• In general, tables are related to each other using foreign key constraints.
• Specify the logical operator to compare values from the columns like =, <, or >.

Note:

JOIN is a technique to bring common rows in one table from another table

Types of Join in SQL

JOIN can be categories in four types in SQL server as mention below

• INNER JOIN: This join return row records if matching values in both tables
• LEFT JOIN: This join return all row records from the left table and the matched records from the right table
• RIGHT JOIN: This join return all row records from the right table, and the matched records from the left table
• FULL JOIN: This join return all row records, when there is a match in either left or right table

So lets understand more of these all joins with examples

INNER JOIN in SQL

INNER JOIN in SQL is the popular join and it returns the common records from both table.

As it is explained in below Venn diagram with two circle represent the tables Customer Table and Order table. It will bring all common Customer ID in both tables.

Example:

Below is the SQL query which described how you can implement INNER join in SQL query command. JOIN work on SELECT command with INNER JOIN keyword

Below query we are performing INNER JOIN on Customer_ID which is available in both tables

SELECT   c.Customer ID, c.Customer_Name,  
         o.Customer_ID, o.Order_Quantity
FROM 
        Customers_Tbl c
        INNER JOIN Orders_Tbl o
              ON c.Customer ID = o.Customer_ID;

The Output of above command will look like as shown below image

LEFT JOIN in SQL

LEFT join returns all row records from the left table and the matching rows from the right table. Left join is also called as left outer join and outer keyword is optional.

Note:

If there is no any matching row in the left table with right table, then columns of the right table will have NULL values.

Example:

Lets take example for Left join with Customer table and Order table

SELECT 
       c.[Customer ID], c.Customer_Name,
       o.[Customer_ID], o.[Order_Quantity]
FROM   
      Customers_Tbl c
         LEFT JOIN Orders_Tbl o
              ON c.[Customer ID] = o.[Customer_ID];

The Output of above command will look like as shown below image

RIGHT JOIN in SQL

Right join returns all row records from the right table and the matching rows from the left table. Right join is also called as Right outer join and outer keyword is optional. You can say it is reveres or opposite of LEFT join in SQL.

Blow is the diagram show Right join.

Note:

If there is no matching row in the Right table with Left table, then columns of the Left table will have NULL values.

Example:

Lets take example for Right join with Customer table and Order table

SELECT
        c.[Customer ID], c.Customer_Name,
        o.[Customer_ID], o.[Order_Quantity]
FROM
        Customers_Tbl c
            RIGHT JOIN Orders_Tbl o
                  ON c.[Customer ID] = o.[Customer_ID];

The Output of above Left join query will look like as shown below image.

so right table have all values but left table which is not matched have NULL values

FULL JOIN in SQL

Full join returns records that contains all rows from both left and right tables, if matching rows from both sides. If there is no match then missing side will return NULL values. FULL join is also called as Full outer join and outer keyword is optional.

Example:

Lets take example for FULL join with Customer table and Order table

SELECT
       c.[Customer ID], c.Customer_Name,
       o.[Customer_ID], o.[Order_Quantity]
FROM
       Customers_Tbl c
         FULL JOIN Orders_Tbl o
              ON c.[Customer ID] = o.[Customer_ID];

Output of Full Join

These are types of JOIN in SQL with examples, which you can use for your SQL query, it is really helpful, if you need any kind of mapping and bring data in one table from another table.