Today's Offer - SQL Server Certification Training - Enroll at Flat 20% Off.

- 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

  1. Inner Join
  2. Outer Join
  3. Cross Join

Outer Joins are again divided as

  1. Left Join or Left Outer Join
  2. Right Join or Right Outer Join
  3. 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

  1. Inner Joins: -

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

Read: What is Complex SQL Queries? Explain Complex SQL Queries with Examples

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

 

Read More: Different Types of SQL Database Functions

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.   Different Type of SQL Joins


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

Different Type of SQL Joins

 

Read: What Is SQL Candidate Key? Difference between Primary Key & Candidate Key

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: Which SQL Server Role you Need to Practice for the Highest Salary in 2020?

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


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

8 days 14 Dec 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

-1 day 05 Dec 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

-1 day 05 Dec 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

0 day 06 Dec 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 20 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

18 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

3 days 09 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

0 day 06 Dec 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

3 days 09 Dec 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews