OUTER JOIN :-
Equi joins returns only matching records but not un-matching records.
To get the un-matching record also,we need to perform,”out-join”.
Outer joins are three types
- Left outer join
- Right outer join
- Full outer join
(I) Left outer join:-
Left outer joins returns all records from left side table and matching records from right side table.
Display enames & deptnames
Select e.ename,d.dname From emp e,dept d Where e.deptno=*.d.deptno
Error because SQL server 2005 doesn’t allow the Non-ANSI std in outerjoin
ANSI STYLE of left outer join
Select e.ename,d.dname From emp e left outer join dept d Or e.deptno= d.deptno
Emp Dept
Eno Name dno Dno dname
1 A 10 10 Alcs
2 B 20 20 Research
3 C 30 30 Sales
4 D 10 40 operation
5 E —
O/P :— ename dname
A ALCS
B Research -> Left Outer
C Sales
D ALCS
E —-
(II) Right outer join :–
It returns all records from right side table and matching records from left side.
Display enames & dnames
Select e.ename,d.dname From emp e right outr join dept d Or e.deptno=d.deptno.
O/P :– ename dname
A ALCS
B Research
C Sales -> Right outer
D ALCS
O Operations
(III) FULL OUTER JOIN :-
- Returns all records from both table.
O/P :–
A ALCS
B Research
C Sales
D ALCS
E ——
— Operation
Select e.ename,d.dname From emp e full outer join dept d On e.deptno=d.deptno.
Non-Ansi std of full outer join {i:e 2000 SQL server}
Select e.ename, d.dname From emp e,dept d Where e.deptno= *d.deptno Union Select e.ename, d.dname From emp e, dept d Where e.deptno *= d.deptno
CROSS JOIN :-
- It returns cross product of two tables.
- SQL server performs cross join when we submit the select statement with alt join condition.
Example :– Orders Discount
Ordamt dis
1,00,000 5%
20%
40%
select ordamt, dis (ordamt- (ordamt*dis/100))
Netamt from orders ,discount.
O/P:– Ordamt dis netamt
1,00,000 7 93,000
1,00,000 10 90,000
1,00,000 12 88,000
1,00,000 13 87,000