SQL Server Joins

Joins are used to retrieve data from multiple table columns. SQL Server joins are of three types.

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

Syntax:

SELECT tablename on conidtion
join type table on condition
join type table on condition

Create following two tables(emp department)

emp table

CREATE TABLE [dbo].[emp](
[empname] [varchar](20) Not NULL,
[empno] [numeric](18, 0) NULL,
[job] [varchar](30) NULL,
[joindate] [date] NULL,
[salary] [money] NULL,
[deptno] [nchar](10) NULL,
)

Emp-table

dept table

CREATE TABLE [dbo].[dept](
[deptno] [nchar](10) NULL,
[dname] [varchar](40) NULL,
[dloc] [varchar](40) NULL
)

Dept-Table

Join Rules

  • Tables should have related columns.
  • Joining tables produce temporary result set with combined structure of both the tables.

Inner Joins: Inner join is default type of join. Inner join produces result set which contains only matched rows.

Examples
1) Display empployee name, number departname and department location

SELECT empname, empno, dname, dloc from emp
INNER JOIN dept on emp.deptno=dept.deptno

2) Display empolyee number, name who are working in accounting department.

select empno, empname from emp
INNER JOIN dept on emp.deptno=dept.deptno
where dept.dname=’Accounting’

Empno Empname
5747 John
5471 David

3) Display empolyee number, name who are working in Mumbai.

select empno, empname from emp
INNER JOIN dept on emp.deptno=dept.deptno
where dept.dloc=’Mumbai’

Result:

empno Empname
5206 Balireddy
5234 Kalpesh
5549 Varshita

Outer Join: Outer Joins produces result set which contain matched rows and unmatched rows. We have three types Outer joins

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Left Outer Join: Result set contains all rows from left side table and matching rows from right side table.

Example

Display employee names and department names.

SELECT e.empname, d.dname from emp e
left outer join dept d
on e.deptno=d.deptno

Result:

Empname Dname
Balireddy Administration
John Accounting
David Accounting
Kalpesh Administration
Karunakar Development
Varshita Administration
Imran Development
George Development

2) Display all the employee names and corresponding department names and location.

select empname, dname, dloc from emp
Left Outer JOIN dept on emp.deptno=dept.deptno

Left-Outer-Join

Right Outer Join: Result set contains all rows from right side table and matching rows from left side table.

Examples
Display employee number and names and all department names and location.

select empno,empname, dname, dloc from emp
Right Outer JOIN dept on emp.deptno=dept.deptno

Right-Outer-Join

Full Outer Join: Result set contains all rows from both the tables.

Example
Display all employee number, names and department names, locations.

select empno, empname, dname, dloc from emp
full Outer JOIN dept on emp.deptno=dept.deptno

Full Outer Join

Cross Join: A join without condition is called Cross Join. Cross join result set contains all rows from first and second tables.

Example

select *from emp cross join dept

Full-Outer-Join

Self Join: The concept of joining a table with itself is called self-join. To work with self-join, we have to use alias table.

Powered by k2schools