Aug 25, 2017 
687.5k
06
DecHalloween Offer : Upto 50% off on Popular Courses + 2 free self-paced courses - SCHEDULE CALL
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.
Let’s understand Join types with examples and the differences between them.
Read More: Different Types of SQL Keys
1.Employee Table (tblEmployee) 
2.Department table (tblDepartment) 
Return only matching rows between both the tables. On matching rows are eliminated. 
Read: Which SQL Server Role You Need to Practice for the Highest Salary ?
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. 
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
Read: All You Need to Know About SQL: SQL Tutorial for Beginners & Experienced
Returns all the matching rows and non-matching row from right table. 
SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee RIGHT JOIN tblDepartment ON tblEmployee.DepartmentId=tblDepartment.Id
Returns all the rows from both left and right of the table, including non-matching rows. 
SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee FULL JOIN tblDepartment ON tblEmployee.DepartmentId=tblDepartment.Id
Cross Join: -
Read: Normalization in SQL | 1NF, 2NF, 3NF and BCNF with Examples
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. 
SELECT Name,Gender,Salary,DepartmentName FROM tblEmployee CROSS JOIN tblDepartment
FaceBook
Twitter
LinkedIn
Pinterest
Email
The JanBask Training Team includes certified professionals and expert writers dedicated to helping learners navigate their career journeys in QA, Cybersecurity, Salesforce, and more. Each article is carefully researched and reviewed to ensure quality and relevance.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
How to Become a Database Administrator? Just Know the 5 Steps
7k
Learn SQL Union All Query Operators with Examples
682.5k
Most Popular SQL Server Performance Tuning Tips
782.8k
What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key
490.4k
A Comprehensive SQL Server Tutorial Guide for Beginners & Experienced
244.1k
Receive Latest Materials and Offers on SQL Server Course
Interviews
Sep 30, 2021
7k
