JOINS :–
- The join is a data retrieval operation performed to get data multiple tables.
DB DWH
tables are normalized tables are de-normalized.
more joins are required less joins are required.
TYPES OF JOINS :-
(i) inner join
- -equi join
- -non – equi join
- -self join
(II) outer join
- -left
- -right
- -full
(III) cross join
(I) inner join in sql :-
(a) Equi join :-
- Joining two tables based on common column.
- To perform equi there should be a common column,but the name of the common column need not be same.
- SQL server performs join operators,when more than one lable is listed in from class.
Example :- Syntax : from emp, dno.
| Syntax :select<collist>from<tablist> Where<join cond>
[and <join cond>and<cond>—–] |
Join condition :- The two tables are joined based on this condition.
Example :- child.fk=parent.pk(if relationship exists)
(or)
Table1.commncol=table2.commcol(if there is no relationship)
- Join condition is based on equal to operation
- To join ‘n’ tables (n-1) join condition are required.
Emp dept .
Empno ename sal d.no d.no dname loc.
1 A 5 10 10 A/c –
2 B 6 20 20 sales –
3 C 4 30 30 purchase –
4 D 3 10 40 market –
5 E 7 –
Display enames and dept names :-
Select ename,dname, From emp, dept, Where emp.dno=dept.no
ename dname
A alcs
B sales
C purchase
D A/C
Note: “In case of join queries better to use table Alias”
Select e.ename, d.dname From emp e,dept d where e.deptno=d.dno.
Display name of the employees working for sales department.
Select e.ename
From emp e,dept d
Where e.deptno=d.dno
And d.dname =’sales’
Display names of the employees working for sales dept& more than earn 3000.
Select e.ename
From emp e,dept d
Where e.deptno=d.dno
And d.dname=’sales’
And sal>3000
Display ename , dname, experience, netsal
Emp Dept emp-expr emp-netsal
Empno dno D-no empno expr empno netsal
1 10 10 1 10 1 10,000
2 20 20 2 12 2 20,000
3 30 30 3 15 3 30,000
4 10 40 4 8 4 40,000
Select E.ename,d.dname,x.expr
From emp e,dept d,emp_expr x
Where e.deptno=d.deptno
And x.empno=e.empno