Blog

Different types of Joins in SQL Server

  • (4.0)
  • | 3775 Ratings

Joins in SQL server is used to integrate rows from multiple datasets, based on a common field between them.

In SQL, joins are primarily used to fetch reference details. For example, employees table is having data for employee details like employee name, number and in which department number he/she is working. Departments table is having data for department number, name and location. Now if we need to find the name of the department in which the employee is working then we need to join both the tables and fetch the filtered records.


We use join majorly to reduce duplication in the result and improve the query performance when multiple data sets are involved in the query.

There are various types of joins available in SQL server. They are as follows.

  1. Inner Join
  2. Outer Join
    • Full Outer Join – also known as Full Join
    • Left Outer Join – also known as Left Join
    • Right Outer Join – also known as Right Join
  3. Self Join
  4. Cross Join

sql server joins

self join & cross join

To understand them more, we will go through each join with examples.

Before going into much detail, let us first have 2 tables with data which we are going to use in joins examples.

Table 1: Emp 

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT null 11/17/1981 5000 100 10
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30
7782 CLARK MANAGER 7839 5/1/1981 2450 100 10
7566 JONES MANAGER 7839 5/1/1981 2975 120 20
7788 SCOTT ANALYST 7566 4/19/1987 3000 120 20
7902 FORD ANALYST 7566 12/3/1981 3000 120 20
7369 SMITH CLERK 7902 12/17/1980 800 120 20
7499 ALLEN SALESMAN 7698 9/28/1981 1600 300 30
7521 WARD SALESMAN 7698 9/28/1981 1250 500 30
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30

Table 2: Dept

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
70 MARKETING ATLANTA

Enthusiastic about exploring the skill set of SQL Server? Then, have a look at the SQL Server Training Course together additional knowledge.

Inner Join

Inner join is used to extract the records which are common between both the tables. In SQL terms, inner join returns all records where join condition is met.

General form of the inner join SQL statement is:

SELECT column-names
FROM table1 INNER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the inner join statement like below:

SELECT *
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7839 KING PRESIDENT null ######## 5000 100 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 4/2/1981 2975 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 30 SALES CHICAGO

Here, in above query output, we can see employee having empno as 7521 and 7654 were not displayed because they have deptno as null. So, they are failing while joining with dept table.

[ Related Article page: SQL Server Tutorial ]

Outer Join

Left Outer Join

As the name implies, left outer join extracts all the records from the left table, whereas from right table, only common records whichever are meeting join condition will be fetched.

General form of left outer join SQL statement is:

SELECT column-names
FROM table1 LEFT OUTER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the left outer join statement like below:

SELECT *
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT null ######## 5000 100 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 4/2/1981 2975 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 ######## 1250 1400 null null null null
7521 WARD SALESMAN 7698 ######## 1250 500 null null null null

Here, in above query output, we can see all the records from left (emp) table were selected irrespective of their deptno values.

Frequently asked SQL Server Interview Questions & Answers

Right Outer Join

As the name implies, right outer join extracts all the records from the right table whereas, from left table, only common records whichever are meeting join condition will be fetched.

General form of right outer join SQL statement is:

SELECT column-names
FROM table1 RIGHT OUTER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the right outer join statement like below:

SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7839 KING PRESIDENT null ######## 5000 100 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 4/2/1981 2975 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 30 SALES CHICAGO
null null null null null null null null 40 OPERATIONS BOSTON
null null null null null null null null 70 MARKETING ATLANTA

Here, in above query output, we can see that all the records from the right (dept) table are fetched and only matched records from left(emp) table are fetched.

Full Outer Join

Full outer join extracts all the records from both the tables irrespective of any condition.
 

General form of full outer join SQL statement is:

SELECT column-names
FROM table1 FULL OUTER JOIN table2 
ON table1.columnname = table2.columnname

For above 2 tables, we can write the right outer join statement like below:

SELECT *
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
7839 KING PRESIDENT null null 5000 100 10 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 7839 5/1/1981 5000 null 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 6/9/1981 2850 100 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 4/2/1981 2450 120 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 ######## 3000 120 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 ######## 800 120 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 ######## 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 ######## 1250 500 null null null null
7654 MARTIN SALESMAN 7698 ######## 1250 1400 null null null null
null null null null null null null null 70 MARKETING ATLANTA
null null null null null null null null 40 OPERATIONS BOSTON

Here, in above query output, we can see all the records from left(emp) and right(dept) table are fetched. Full outer join is also termed as join of left and right outer join.

Self Join

Self join is a simple join with the same table itself. It is mainly used when the hierarchy is involved, or

some relationships are there between records in the same table. For example, each employee will have one manager, and each manager is an employee as well. So for each manager, there will be a record in employee table.

General form of self join SQL statement is:

SELECT column-names
FROM table1 t1 JOIN table1 t2 
ON t1.columnname = t2.columnname

For above 2 tables, we can write the self join statement like below:

SELECT e1.*, e2.empno "MGR EMPNO", e2.ename "MGR ENAME"
FROM emp e1 JOIN emp e2
ON e1.mgr = e2.empno

Query Output:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO MGR EMPNO MGR ENAME
7902 FORD ANALYST 7566 12/3/1981 3000 120 20 7566 JONES
7788 SCOTT ANALYST 7566 4/19/1987 3000 120 20 7566 JONES
7521 WARD SALESMAN 7698 2/22/1981 1250 500 null 7698 BLAKE
7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 null 7698 BLAKE
7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30 7698 BLAKE
7566 JONES SALESMAN 7839 4/2/1981 2975 120 20 7839 KING
7782 CLARK MANAGER 7839 6/9/1981 2450 100 10 7839 KING
7698 BLAKE MANAGER 7839 5/1/1981 2850 null 30 7839 KING
7369 SMITH CLERK 7902 12/17/1980 800 120 20 7902 FORD

Here, in query output, we can see employee details with his manager details by joining the emp table with itself.

Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Cross Join

Cross join is a cartesian product of two tables. It will connect all rows of left table to each row of right table. So, query result of a cross join is number of rows in left table multiplied by number of rows in right table.
 

General form of cross join SQL statement is:

SELECT column-names
FROM table1 t1 CROSS JOIN table1 t2 

For above 2 tables, we can write the cross join statement like below:

SELECT count(*) 
FROM emp CROSS JOIN dept;

Query Output:

50
50 = (no. of rows in emp table) * (no. of rows in dept table)


Conclusion

Joins are the backbone of any database. Hope this article has provided with the required insights on various types of joins and when and how to use it.


Subscribe For Free Demo

Free Demo for Corporate & Online Trainings.

Arogyalokesh
About The Author

Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.


DMCA.com Protection Status

Close
Close