Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- 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: Top 20 SSRS Interview Questions And Answers For 2024 (Freshers And Experienced)

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: Top 50 Informatica interview questions you should prepare

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

Cross Join: -

Read: SQL Server Recovery Models-Simple, Full and Bulk Log

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

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    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.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

16 days 05 Jul 2024

QA Course

QA

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

Upcoming Class

8 days 27 Jun 2024

Salesforce Course

Salesforce

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

Upcoming Class

7 days 26 Jun 2024

Business Analyst Course

Business Analyst

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

Upcoming Class

9 days 28 Jun 2024

MS SQL Server Course

MS SQL Server

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

Upcoming Class

9 days 28 Jun 2024

Data Science Course

Data Science

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

Upcoming Class

2 days 21 Jun 2024

DevOps Course

DevOps

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

Upcoming Class

5 days 24 Jun 2024

Hadoop Course

Hadoop

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

Upcoming Class

2 days 21 Jun 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

17 days 06 Jul 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

10 days 29 Jun 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

23 days 12 Jul 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

2 days 21 Jun 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews