Types of SQL Server Joins With Examples

This article describe types of SQL server joins with examples. You can retrieve data from more than one table together as a part of single result set.


Types of Joins

  1. Inner Join
  2. Outer Join
    • Left outer Join
    • Right outer Join
    • Full outer Join
  3. Cross Join
  4. Equi Join
  5. Self Join

To understand examples of SQL joins we will create sample tables and insert some values to it.

Open your SQL server and execute below SQL statements to create EmployeeDetails and EmpSalary sample tables.

        
CREATE TABLE EmployeeDetails (
  EmpId int PRIMARY KEY,
  EmpFirstName varchar(50),
  EmpLastName varchar(50),
  Department varchar(50),
  DepartID int
)

CREATE TABLE EmpSalary (
  EmpID int,
  EmpFullName varchar(80),
  EmpSalary int,
  EmpWorkingYears int
)
        
    

Execute below SQL to insert sample data to EmployeeDetails table

        
    INSERT INTO EmployeeDetails
      VALUES (1001, 'Bhavana', 'Sharma', 'IT', 2)
    INSERT INTO EmployeeDetails
      VALUES (1002, 'Varun', 'Sharma', 'IT', 2)
    INSERT INTO EmployeeDetails
      VALUES (1003, 'Jaspreet', 'Kaur', 'Accounts', 3)
    INSERT INTO EmployeeDetails
      VALUES (1004, 'Shruti', 'Kalia', 'HR', 1)
    INSERT INTO EmployeeDetails
      VALUES (1005, 'Shaili', 'Verghese', 'IT', 2)
    INSERT INTO EmployeeDetails
      VALUES (1006, 'Rakesh', 'Dubey', 'Accounts', 3)
        
    

EmpSalary

        
    INSERT INTO EmpSalary
        VALUES (1001, 'Bhavana Sharma', 35000, 3)
    INSERT INTO EmpSalary
      VALUES (1002, 'Varun Sharma', 25000, 2)
    INSERT INTO EmpSalary
      VALUES (1003, 'Jaspreet Kaur', 20000, 2)
    INSERT INTO EmpSalary
      VALUES (1004, 'Shruti Kalia', 18000, 1)
    INSERT INTO EmpSalary
      VALUES (1005, 'Shaili Verghese', 25000, 2)
    INSERT INTO EmpSalary (EmpFullName, EmpSalary, EmpWorkingYears)
      VALUES ('Ramesh Kumar', 6000, 1)
        
    

If you make a select query on those tables you will get all rows from one table.

         SELECT  * FROM EmployeeDetails
    

SQL Server Join example

         SELECT  * FROM EmpSalary        
    

SQL Server Select


  1. INNER JOIN

    The inner join returns record from multiple tables by applying a comparison operator on a common column. Only rows with values satisfying the join conditions are displayed as resultset. You can use joins with output of table valued user defined functions.

    Syntax of INNER JOIN

            SELECT Column1, [ column2]
            From Table_name1 INNER JOIN Table_name2
            On Table_name1.column =Table_name2.column
        

    With example of EmployeeDetails and EmpSalary if you make a join on EmpID column, you will get rows where EMPID exists in both tables.

    Below query shows how to use INNER JOIN to get matching rows from EmployeeDetails and EmpSalary.

        SELECT
            EmployeeDetails.EmpId,
            EmployeeDetails.EmpFirstName,
            EmpSalary.EmpSalary
        FROM EmployeeDetails
        JOIN EmpSalary
            ON EmployeeDetails.EmpId = EmpSalary.EmpID
    

    It returns below output

    SQL Server INNER JOIN example

    You can also get some variation in result using SQL Server Cross Apply and Outer apply.


  2. OUTER JOIN

    An Outer Join returns record containing all the rows from one table and the matching rows from the other.

    Syntax for Outer Join

        SELECT Column1, [Column2]
        FROM Table_name1 [LEFT | RIGHT | FULL] OUTER JOIN Table_name2
        ON Table_name1.column = Table_name2.column
                
    • LEFT OUTER JOIN

      The Left Outer Join returns all the rows from the table specified on the LEFT and the matching rows from the table specified on the RIGHT side of the LEFT OUTER JOIN keyword. NULL values are displayed in the columns of the right side table where matching rows are not found with the left side table.

      Example: The below given query returns all the rows from left table (EmployeeDetails) and the matching data from the right table (EmpSalary), where the data does not match NULL value is displayed.

          SELECT
              e.EmpId,
              e.EmpFirstName,
              e.DepartID,
              s.EmpSalary
          FROM EmployeeDetails e
          LEFT OUTER JOIN EmpSalary s
              ON e.EmpId = s.EmpID
          

      It returns below output

      sql left outer join example

    • RIGHT OUTER JOIN

      The Right Outer Join returns all the rows from the table specified on the RIGHT and the matching rows from the table specified on the LEFT side of the RIGHT OUTER JOIN keyword.

      NULL values are displayed in the columns of the left side table where matching rows are not found with the right side table.

      Example: The below given query returns all the rows from right table (EmpSalary) and the matching data from the left table (EmployeeDetails), where the data does not match NULL values are displayed.

          SELECT
              e.EmpId,
              e.EmpFirstName,
              e.DepartID,
              s.EmpSalary
          FROM EmployeeDetails e
          RIGHT OUTER JOIN EmpSalary s
              ON e.EmpId = s.EmpID
          

      It returns below output.

      sql server outer join example

    • FULL OUTER JOIN

      A Full Outer Join is a combination of left and right outer join. This join returns all the matching and non-matching values from both the tables. However in case of non-matching values a NULL value is displayed.

      Example Here in this case all the data from both the tables is displayed, however non matching values are displayed as NULL

          SELECT
            e.EmpId,
            e.EmpFirstName,
            e.DepartID,
            s.EmpSalary
          FROM EmployeeDetails e
          FULL OUTER JOIN EmpSalary s
            ON e.EmpId = s.EmpID
          

      It returns below output

      sql full outer join example


  3. CROSS JOIN

    The Cross Join can also be called as Cartesian Product of the two tables. The result would be the number of rows in the first table multiplied by the number of rows in the second table.

    If you have 4 rows in first table and 3 rows in second table, you will get 12 rows when you have cross join on both table.

    Syntax: SELECT Column1, [column2] FROM Table_name1 CROSS JOIN Table_name2

    Example: The below example table Computer have computer details and table Addon have other devices details. If we want to know the cost of computer with all the addons, Cross join can be used for the results.

      CREATE TABLE Computer (
          CompID int,
          computerDes varchar(100),
          Price int
      )
    
      INSERT INTO computer
        VALUES (1, 'Pentium 4,1GB RAM', 25000)
      INSERT INTO computer
        VALUES (2, 'Dual Core,2GB RAM', 35000) 
                

    Addon Table

       CREATE TABLE Addon (
          ID INT,
          Description VARCHAR(100),
          Price INT
       )     
    
       INSERT INTO Addon
          VALUES (1, 'Speakers', 5000)
       INSERT INTO Addon
          VALUES (2, 'printer', 15000)
                

    You will use CROSS JOIN which to get computer and additional product price.

       SELECT
        computer.computerDes,
        Addon.description,
        computer.Price + Addon.price AS totalprice
      FROM Computer
        CROSS JOIN Addon
                

    Output of CROSS JOIN

    SQL Server CROSS JOIN example

  4. EQUI JOIN

    An Equi Join is same as inner join , it joins the tables with the help of a foreign key. However an equi join differs from inner join in only one way, an equi join display all the column from both the tables.

    Syntax

      SELECT *
        FROM EmployeeDetails JOIN EmpSalary
        ON EmployeeDetails.EmpId = EmpSalary.EmpID
                

    Output of Equi Join

    sql server equi join example


  5. SELF JOIN

    In a self join a table is joined with itself. In this join one row in a table correlates with other row in same table. It is important to give different alias name to the tables as same table is going to be used twice.

    Syntax

        SELECT a.Column1, [b.column2]
        FROM Table_name1 a JOIN Table_name1 b
        WHERE a.column =b.column
                

    Example Considering EmpDetails table below, we can apply self join on EmpID and ManagerID

    Below is select all output of EmployeeDetails table

    sql server select all rows

    Below query makes self join to get manager details saved in same table.

        SELECT a.EmpId, a.EmpFirstName AS ManagerName,
        b.EmpId AS ManagerID,b.EmpFirstName AS EmployeeName
        FROM EmployeeDetails a , EmployeeDetails b
        WHERE a.EmpId = b.ManagerID
                

    Below is the output of SELF JOIN

    sql server self join example

Download Source code

Speak your mind :
Leave a comment for this article on dotnetbloogers.com
User profile picture on dotnetmentors.com

by daisyeden at 11/20/2015 4:09:00 AM
Really enjoying your sharing, you have a great teaching style and make these new concepts much easier to understand. Thanks.
post comment on dotnetmentors.com