Joins in sql server - Part 12

Joins in SQL server are used to query (retrieve) data from 2 or more related tables. In general tables are related to each other using foreign key constraints.

Please watch Parts 3 and 5 in this video series, before continuing with this video.

Part 3 - Creating and working with tables
Part 5 - Cascading referential integrity constraint






In SQL server, there are different types of JOINS.
1. CROSS JOIN
2. INNER JOIN 
3. OUTER JOIN 

Outer Joins are again divided into 3 types
1. Left Join or Left Outer Join
2. Right Join or Right Outer Join
3. Full Join or Full Outer Join

Now let's understand all the JOIN types, with examples and the differences between them.
Employee Table (tblEmployee)


Departments Table (tblDepartment)


SQL Script to create tblEmployee and tblDepartment tables
Create table tblDepartment
(
     ID int primary key,
     DepartmentName nvarchar(50),
     Location nvarchar(50),
     DepartmentHead nvarchar(50)
)
Go

Insert into tblDepartment values (1, 'IT', 'London', 'Rick')
Insert into tblDepartment values (2, 'Payroll', 'Delhi', 'Ron')
Insert into tblDepartment values (3, 'HR', 'New York', 'Christie')
Insert into tblDepartment values (4, 'Other Department', 'Sydney', 'Cindrella')
Go

Create table tblEmployee
(
     ID int primary key,
     Name nvarchar(50),
     Gender nvarchar(50),
     Salary int,
     DepartmentId int foreign key references tblDepartment(Id)
)
Go

Insert into tblEmployee values (1, 'Tom', 'Male', 4000, 1)
Insert into tblEmployee values (2, 'Pam', 'Female', 3000, 3)
Insert into tblEmployee values (3, 'John', 'Male', 3500, 1)
Insert into tblEmployee values (4, 'Sam', 'Male', 4500, 2)
Insert into tblEmployee values (5, 'Todd', 'Male', 2800, 2)
Insert into tblEmployee values (6, 'Ben', 'Male', 7000, 1)
Insert into tblEmployee values (7, 'Sara', 'Female', 4800, 3)
Insert into tblEmployee values (8, 'Valarie', 'Female', 5500, 1)
Insert into tblEmployee values (9, 'James', 'Male', 6500, NULL)
Insert into tblEmployee values (10, 'Russell', 'Male', 8800, NULL)
Go

General Formula for Joins
SELECT      ColumnList
FROM           LeftTableName
JOIN_TYPE  RightTableName
ON                 JoinCondition

CROSS JOIN
CROSS JOIN, produces the cartesian product of the 2 tables involved in the join. For example, in the Employees table we have 10 rows and in the Departments table we have 4 rows. So, a cross join between these 2 tables produces 40 rows. Cross Join shouldn't have ON clause. 

CROSS JOIN Query:
SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
CROSS JOIN tblDepartment

JOIN or INNER JOIN
Write a query, to retrieve Name, Gender, Salary and DepartmentName from Employees and Departments table. The output of the query should be as shown below.


SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
INNER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

Note: JOIN or INNER JOIN means the same. It's always better to use INNER JOIN, as this explicitly specifies your intention.

If you look at the output, we got only 8 rows, but in the Employees table, we have 10 rows. We didn't get JAMES and RUSSELL records. This is because the DEPARTMENTID, in Employees table is NULL for these two employees and doesn't match with ID column in Departments table.

So, in summary, INNER JOIN, returns only the matching rows between both the tables. Non matching rows are eliminated.

LEFT JOIN or LEFT OUTER JOIN
Now, let's say, I want all the rows from the Employees table, including JAMES and RUSSELL records. I want the output, as shown below.


SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
LEFT JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

Note: You can use, LEFT JOIN or LEFT OUTER JOIN. OUTER keyowrd is optional

LEFT JOIN, returns all the matching rows + non matching rows from the left table. In reality, INNER JOIN and LEFT JOIN are extensively used.

RIGHT JOIN or RIGHT OUTER JOIN 
I want, all the rows from the right table. The query output should be, as shown below.


SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
RIGHT JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

Note: You can use, RIGHT JOIN or RIGHT OUTER JOIN. OUTER keyowrd is optional

RIGHT JOIN, returns all the matching rows + non matching rows from the right table.

FULL JOIN or FULL OUTER JOIN
I want all the rows from both the tables involved in the join. The query output should be, as shown below.



SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL OUTER JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

OR

SELECT Name, Gender, Salary, DepartmentName
FROM tblEmployee
FULL JOIN tblDepartment
ON tblEmployee.DepartmentId = tblDepartment.Id

Note: You can use, FULLJOIN or FULL OUTER JOIN. OUTER keyowrd is optional

FULL JOIN, returns all rows from both the left and right tables, including the non matching rows.

Joins Summary


8 comments:

  1. I have a doubt.... In which case we use cross join??

    ReplyDelete
    Replies
    1. Consider you have Car table which holds model information Car(Make, Model) and AvaliableColorOption(Color)
      All available car options can be achieved by cross join..
      Car table:
      1. Benz C-Class
      2. Benz S-Class

      AvaliableColorOption:
      1. Red
      2. Green

      Cartesian Product of the tables will yield:
      1. Benz C-Class Red
      2. Benz S-Class Red
      3. Benz C-Class Green
      4. Benz S-Class Green

      This may not be the best example, but it just came to my mind on fly just wanted to throw it out there to check if it helps.

      Delete
    2. @Vikul, To add a one more point. I read somewhere, If you want to have a huge test data . you can use cross join.

      Delete
  2. Hello,
    Thanks for videos.
    Could you please show how can we join three tables (e.g. table1 (Name, CityID), table2 (CityID, CityName, TerritoryID), table3 (TerritoryID, TerritoryName) we will show Name and TerritoryName)
    Thank you in advance.

    ReplyDelete
  3. HI,
    Try this :-

    Select dbo.Table1.Name,dbo.Table3.TerritoryName
    from dbo.Table1
    join Table2
    on dbo.Table2.CityID = dbo.Table1.CityId
    join Table3
    on dbo.Table2.TerritoryId = dbo.Table3.TerritoryId

    ReplyDelete
  4. Could you please explain how to use Cross apply and outer apply , In which scenario we can use these two. Thank you.

    ReplyDelete
  5. Hi sir Can u make power bi and DAX videos

    ReplyDelete
  6. FOR ANYONE WHO NEEDS THOSE TABLES
    Create Table TableEmployer
    (
    ID int identity(1,1)Primary Key NOT NULL,
    Name nchar(50) Not NULL,
    Gender nchar(10) NOT NULL,
    Salary int Not NULL,
    DepartamentID int
    )

    Create Table TableDepartament
    (
    ID int identity(1,1)Primary Key,
    DepartamentName nchar(50) Not NULL,
    Location nchar(59) Not Null,
    DepartamentHead nchar(50) Not Null,

    )
    Alter Table TableEmployer
    Add Constraint CK_TableEmployer_Salary
    CHECK(Salary > 0)
    Alter Table TableEmployer
    ADD CONSTRAINT CK_TableEmployer_Gender
    CHECK(Gender = 'Female' OR Gender = 'Male' OR Gender ='Unknown')
    ALTER TABLE TableEmployer
    ADD CONSTRAINT CK_TableEmployer_DepartamentID
    CHECK(DepartamentID>=1 AND DepartamentID<=4)
    INSERT INTO TableEmployer values ('Tom','Male',4000,1) */
    Insert into TableEmployer values('Ron','Male',4000,1)
    Insert into TableEmployer values('Jessie','Female',4800,3)
    Insert into TableEmployer values('Pam','Female',3500,1)
    Insert into TableEmployer values('John','Male',3000,2)
    Insert into TableEmployer values('Sam','Male',4500,2)
    Insert into TableEmployer values('Linlna','Female',7300,3)
    Insert into TableEmployer values('Artesa','Female',4000,2)
    Insert into TableEmployer values('Dom','Male',5500,1)
    Insert into TableEmployer values('Mayer','Male',9000,3)
    Insert into TableEmployer values('Aryueta','Female',3000,2)
    Insert into TableEmployer values('Bangklor','Male',4560,1)

    Select Name ,Gender ,Salary ,DepartamentID
    From TableEmployer
    INNER JOIN TableDepartament
    ON TableEmployer.DepartamentID = TableDepartament.ID

    Select Name ,Gender ,Salary ,DepartamentID
    From TableEmployer
    LEFT JOIN TableDepartament
    ON TableEmployer.DepartamentID = TableDepartament.ID

    Select Name ,Gender ,Salary ,DepartamentID
    From TableEmployer
    RIGHT OUTER JOIN TableDepartament
    ON TableEmployer.DepartamentID = TableDepartament.ID

    Select Name ,Gender ,Salary ,DepartamentID
    From TableEmployer
    FULL OUTER JOIN TableDepartament
    ON TableEmployer.DepartamentID = TableDepartament.ID

    Select Name ,Gender ,Salary ,DepartamentID
    From TableEmployer
    CROSS JOIN TableDepartament
    -- doesn't need a clause ON TableEmployer.DepartamentID = TableDepartament.ID
    -- CROSS PRODUCES CONTATION PRODUCTS LEFTTABLE X RIGHTTABLE
    -- IT TAKES EACH RECORD FROM LEFT TABLE AND ASSOCIATES WITH RIGHT TABLE

    /*Select ColumnList
    From LeftTable
    JoinType RighTable
    ON JoinCondition */ -- GENERIC IDEA ON HOW TO CREATE A JOIN
    Select Name,Gender,Salary ,DepartamentID
    From TableEmployer
    /* any join */ INNER JOIN TableDepartament
    ON TableEmployer.DepartamentID = TableDepartament.ID

    ReplyDelete

It would be great if you can help share these free resources