SQL_JOIN ALL TYPE OF JOINS OF MYSQL.PPTX

it is a ppt of SQLl join.

1. SQL Joins MAHESHWARI BATCH-10(OCTOBER)

2. WHAT IS JOINS   A JOIN  clause is used to combine rows from two or more tables, based on a related column between them . Used to fetch the data from multiple tables by using a single select query; types: i ) equi /inner join ii)non- equi join iii) Left outer join iv) right outer join v) full outer join vi) self join

3. tables Dept Table: : create table dept( deptno Integer PRIMARY KEY, dname VARCHAR(10)); insert into dept values(30,'HR'); insert into dept values(20,'IT'); insert into dept values(10,'ACC'); insert into dept values(40,'MKT'); Emp Table: create table emp1( eid INTEGER PRIMARY KEY, ename VARCHAR(20), deptno INTEGER, mgr INTEGER, CONSTRAINT emp1_deptno_dept_deptno_fky foreign key( deptno ) references dept( deptno )); insert into emp1 values(1,'scott',10,5); insert into emp1 values(2,'john',20,1); insert into emp1 values(3,'mark',30,4); insert into emp1 values(4,'rose',null,5); insert into emp1 values(5,'bush',null,null);

4. table mysql > select * from emp1; +-----+-------+--------+------+ | eid | ename | deptno | mgr | +-----+-------+--------+------+ | 1 | scott | 10 | 5 | | 2 | john | 20 | 1 | | 3 | mark | 30 | 4 | | 4 | rose | NULL | 5 | | 5 | bush | NULL | NULL | +-----+-------+--------+------+ 5 rows in set (0.00 sec) mysql > select * from dept; +--------+-------+ | deptno | dname | +--------+-------+ | 10 | ACC | | 20 | IT | | 30 | HR | | 40 | MKT | +--------+-------+

5. TYPES OF JOINS i ) equi /inner join : perfrom equality check -------------------- ->fetches only the common data from the table. select e.eid,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno ; ii) non- equi join :uncommon data ----------------- select e.eid,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno != d.deptno ;

6. iii ) left outer join = equi join + unmatched recoreds of left table. -------------------- select e.eid,e.ename,e.deptno,d.dname from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno ; iv ) right outer join : equi join + unmatched records from right table --------------------- select e.eid,e.ename,d.deptno,d.dname from emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno ;

7. v)full outer join : equi join + unmatched recoreds of left table + unmatched recoreds of right table ------------------ select e.eid,e.ename,d.deptno,d.dname from emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno ; // error(Full outer join is not supported by Mysql ) UNION select e.eid,e.ename,e.deptno,d.dname from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno UNION select e.eid,e.ename,d.deptno,d.dname from emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno ;

8. vi ) self join: ---------------- select e.ename ' employees',m.ename 'Manager' from emp e,emp m where e.mgr=m.eid;

9. H o w C a n W e D o Th i s i n M ySQ L ? MySQL doesn’t have FULL OUTER JOIN Simulate using UNION, LEFT and RIGHT JOINs SELECT * FROM TableA LEFT JOIN TableB ON TableA.PK = TableB.PK WHERE TableB.PK IS NULL UNION SELECT * FROM TableA RIGHT JOIN TableB ON TableA.PK = TableB.PK WHERE TABLEA.PK IS NULL

10. THANK YOU