JOINS IN SQL SERVER

Dept Table

EMP TABLE

Joins

Retrieving data from multiple tables using single select statement is called as Join

To perform joins you can use either on ANSI syntax or NON ANSI syntax.

ANSI syntax[American National Standard Institute]

Select * /<collist> from <table1> from inner/outer/cross join <table2> on [<join conditions> [inner/outer/cross join] <table3> [<on join condition>]

NON ANSI SYNTAX

Select */ <collist> from <table1> ,<table2> <table3>  [where conditions>]

Joins are classified into three types

·       Inner join

·       Outer join

·       Cross join

Inner join: is the join that displays only the rows that satisfy the given condition

These are again classified into four types

Equi join
non equi join
self join
natural join

Equi join: that uses equal operator in the join condition

Examples:

Display Employee Name and dname?

select ename,DNAME from emp, DEPT where EMP.DEPTNO=DEPT.DEPTNO

or

select* fromemp e inner join dept d on e.DEPTNO=d.DEPTNO

Display the names of employees working for sales?

select e.ename from emp e ,dept d where e.DEPTNO=d.DEPTNO and d.DNAME='sales'

select e.ename from emp e inner join dept d on e.DEPTNO=d.DEPTNO and d.DNAME='sales'

or

select* fromemp e inner join dept d on e.DEPTNO=d.DEPTNO

Display the names of employees working for sales?

select e.ename from emp e ,dept d where e.DEPTNO=d.DEPTNO and d.DNAME='sales'

select empno,ename,sal,deptno ,dname,loc

select e.EMPNO ,e.ENAME, e.sal,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC      from   emp e inner join dept d on e.DEPTNO=d.DEPTNO

Joining three tables

select e.ename ,d.dname ,x.expr from emp e inner join dept d on e.DEPTNO=d.DEPTNO inner join emp_expr x on x.empno=e.EMPNO

select e.EMPNO,e.ENAME,e.SAL,e.DEPTNO,d.DEPTNO,d.DNAME,d.LOC,dd.did,dd.comments from emp e inner join dept d on e.DEPTNO=d.DEPTNO

 inner join deptdetails dd on d.DEPTNO=dd.deptno

Natural join: A join is said to natural join only if it satisfies three conditions

§  Join must be equi join

§  All common cols in the table must be in the join condition

§  Only one set of common col to be displayed in the output

Display the details of EMP along with the DEPT name and location in which EMP is working

SELECT E.*, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

           OR

SELECT E.*, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO

Non Equi join- That does not uses equal operator in the join condition.

Display the details of employees along with grade of the employee based on salary?

select empno,ename, sal,losal,hisal,grade from emp inner join SALGRADE  on sal between losal and HISAL

Display the names of employees whose grade=3

select e.ename from emp e inner join salgrade g on e.sal between g.losal and g.hisal and g.grade=3

self join

The same table must be listed twice in different alias.

A self-join is joining the table to itself.

To perform self-join the same table must be listed twice in different alias.

Self join is performed in table having self referential integrity.

Display the details of employees along with the manager name of the employee?

select  e.EMPNO,e.ename ,e.job,e.mgr ,m.ename as manager from emp e  inner join emp m on e.MGR=m.EMPNO

To get the record who is not having mgr as null then we have to use left outer join as follows

select e.empno,e.ENAME,e.job,e.mgr,m.ename as manager   from emp e left outer join emp m on e.MGR=m.EMPNO

output:

Outer Join

Is the join that can display the rows that does not satisfy the given join condition along with the rows that satisfy the given join condition.

Outer join are classified into three types

1 left outer join

2 right outer join

3 full outer join

 Left outer join

It returns all records from the left side table and matching records from right side table.

To perform left outer join with ANSI syntax use the keyword “left outer join” with Non ANSI syntax use the operator *= in the join condition.

Display the details of employee along with deptname, location in which employee is working and also display the details of employee who are working in department that is not in dept. table.?

select * from emp e left outer join dept d on e.DEPTNO=d.DEPTNO

NON ANSCI

select * from emp e ,dept d where e.DEPTNO*=d.DEPTNO

Right outer join

It returns all the records from right side table and matching records from left side.

To perform right outer join with ANSI syntax use the keyword “RIGHT OUTER JOIN” and to perform Right Outer Join with NON ANSI syntax use the operator “=*”

Display the details of employee along with deptname and location in which the employee is working and also display the details of departments in which there are no employees.

Non ansci

select * from emp e ,dept d where e.DEPTNO=*d.DEPTNO

ansi

select * from emp e right outer join dept d on e.DEPTNO=d.DEPTNO

Full outer join

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

To perform ansi syntax use the keyword  “full outer join” and to perform full outer join with NON ANSI syntax perform “ UNION ON LEFT OUTER JOIN AND RIGHT OUTER JOIN STATEMENT”

Example

Display the details of employees along with department name and location in which employee is working and also display details of employees who are working in a department that is not in department table and the details of employee in which there are no employee.

select * from emp e full outer join dept d on e.DEPTNO=d.DEPTNO

non ansci syntax

SELECT * FROM EMP E  , DEPT D WHERE E.DEPTNO*=D.DEPTNO

UNION

SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO=*D.DEPTNO

NOTE:   NON ANSI SYNTAX OF OUTER JOIN IS NOT SUPPORTED FROM SQL SERVER 2005 ONWARDS EVEN IF IT SUPPORTED, IT WORKS WITH ONLY “EQUAL OPERATOR”

Left Excluding JOIN

This will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

SELECT D.*, E.*

FROM DEPT D

LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO

WHERE E.DEPTNO IS NULL

Right Excluding JOIN

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

SELECT D.*,E.*

FROM EMP E

RIGHT JOIN DEPT  D ON D.DEPTNO = E.DEPTNO

WHERE D.DEPTNO IS NULL

Outer Excluding JOIN

This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. 

Cross Join

Is the join that joins every row in first table with every row in second table with out a join condition.

The result of cross join is called as Cartesian product.

Examples:

Display the details of employees along with every department available in department table?

Select * from emp cross join dept order by empno

Non ansci syntax

select * from emp,dept

Note:

Rule for NON ANSI join

A rule to follow while using Non Ansi syntax of join is no of join conditions in the where clause must be min one less than no of table you are joining and within these conditions all tables  which we are joining must be included otherwise it will perform cross join.

Examples of Joins

Display the details of employees along with the department name and location in which employee is working, manager name of employee, grade of employee based on salary?

select e.EMPNO,e.ENAME,m.ENAME as manager,e.SAL,losal,hisal,grade,e.DEPTNO,dname  from emp e left outer join emp m

on e.MGR=m.EMPNO inner join salgrade on e.sal between losal and HISAL  inner join dept d on e.DEPTNO=d.DEPTNO

non ansi

select e.EMPNO,e.ENAME,e.MGR,m.ENAME as manager,e.SAL,losal,hisal, GRADE,e.DEPTNO,dname,LOC        

 from emp e, emp m,salgrade,dept d where e.MGR=m.EMPNO and e.SAL between losal and HISAL

 and e.DEPTNO=d.DEPTNO