MERGE JOIN

MERGE JOIN IN SQL SERVER

MERGE INTO EMPP as table1 USING (select * from EMP) as table2  ON table1.EMPNO=table2.EMPNO
WHEN MATCHED THEN UPDATE SET table1.ENAME=table2.ENAME,table1.JOB=table2.JOB,table1.MGR=table2.MGR,table1.HIREDATE=table2.HIREDATE,table1.SAL=table2.SAL,table1.COMM=table2.COMM,table1.DEPTNO=table2.DEPTNO
WHEN NOT MATCHED THEN
INSERT VALUES(table2.EMPNO,table2.ENAME,table2.JOB,table2.MGR,table2.HIREDATE,table2.SAL,table2.COMM,table2.DEPTNO);

/***PRINT THE DEPT WISE SALRIES WHOSE SALARY IS GRATER THAN DEPT WISE AVERAGE SALARY***/
WITH X AS
(
SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO
)SELECT * FROM X
INNER JOIN EMP ON X.DEPTNO=EMP.DEPTNO WHERE SAL>AVGSAL