PRIDE MONTH ALERT : FLAT 20% OFF On Our Best-Selling Courses Use - PRIDE20

Select Course
Blog
Corporate Training

+1 202 599 3842

(4.8/5 ) | 1.5K+ Ratings

- SQL Server Blogs -

Different Type of SQL Joins

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

In SQL server, there are different types of joins

Inner Join
Outer Join
Cross Join

Outer Joins are again divided as

Left Join or Left Outer Join
Right Join or Right Outer Join
Full join or Full Outer Join

Let’s understand Join types with examples and the differences between them.

Read More: Different Types of SQL Keys

1.Employee Table (tblEmployee) Different Type of SQL Joins

2.Department table (tblDepartment) Different Type of SQL Joins

Inner Joins: -

Return only matching rows between both the tables. On matching rows are eliminated. Different Type of SQL Joins

Read: Windows SQL Function-All you Need to Know


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

If you look at the output we got only 8 rows but in Employee table it has 10 rows. We didn’t got James and Russell records. This is because DepartmentId, in Employee table is NULL for these two employees and doesn’t match ID column in Department table. Different Type of SQL Joins

2.Left Join or Left Outer Join: -

Returns all the matching rows and non-matching row from left table. In reality, LEFT JOIN and INNER JOIN are extensively used.


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

Different Type of SQL Joins

Read: How to Create Database in Microsoft SQL Server?

3.Right Join or Right Outer: -

Returns all the matching rows and non-matching row from right table. Different Type of SQL Joins


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

Different Type of SQL Joins

4. Full Join or Full Outer Join: -

Returns all the rows from both left and right of the table, including non-matching rows. Different Type of SQL Joins


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

Different Type of SQL Joins

Read: What is the Difference Between Control Flow & Data Flow in SSIS?

Cross Join: -

Read More: Different Types of SQL Injection

Cross Join produces the cartesian product of the two tables involved in the join. For example, in the employee table we have 10 records and in the department table we have 4 records. So as a cross join between two tables it will produce 40 records. Cross join shouldn’t have ON clause. Different Type of SQL Joins


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

SQL Tutorial Overview

Introduction

SQL Server Operator and Fuction

SQL Server Keys

SQL Server Queries

Differentiation

Career

Interview

Useful Resource

Tutorial

FaceBook

Twitter

Google+

Janbask Training

A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.