SQL Server Joins

Joins in SQL server is used to integrate rows from multiple datasets, based on a common field between them. In SQL Server Joins are primarily used to fetch reference details. For example, the employee table is having data for employee details like employee name, number, and in which department number he/she is working. The department's table is having data for department numbers, names, and locations. 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.

Different Types Of SQL Server Joins

Inner Join Outer Join Self Join Cross Join

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

SQL server joins with examples

There are various types of joins available in SQL servers. 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

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

want to build your career with a SQL Server certified professional, Then enroll in our "Best SQL Server Training" This will help you to achieve excellence in this domain.

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

1. 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 the above query output, we can see employees having empno as 7521 and 7654 were not displayed because they have deptno as null. So, they are failing while joining with the dept table.

2. Outer Join

Left Outer Join

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

The general form of the 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.

Explore: 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 the left table, only common records whichever are meeting the join conditions will be fetched.

General form of the 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.

3. 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 on the 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 

4. Cross Join

Cross join is a cartesian product of two tables. It will connect all rows of the left table to each row of the right table. So, the query result of a cross join is a number of rows in the left table multiplied by the number of rows in the 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)

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

Conclusion

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