SIMPLE SINARIAS&diff b/w IN and EXIST

/*TOP SAL DEPT WISE*/

SELECT * FROM EMP E WHERE E.SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO)

-----TOP SAL

SELECT TOP 1 SAL AS TOPP FROM EMP ORDER BY SAL DESC


/* DEPT WISE SAL ABOVE AVG SAL DEPT WISE*/


SELECT * FROM EMP E WHERE E.SAL >(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO)

/*NOT EMPTY DEPT*/

SELECT * FROM DEPT D WHERE EXISTS  (SELECT * FROM EMP WHERE DEPTNO=D.DEPTNO)


/* EMPTY DEPT*/

SELECT * FROM DEPT D WHERE  NOT EXISTS  (SELECT * FROM EMP WHERE DEPTNO=D.DEPTNO)


SELECT DEPTNO ,
 STUFF((SELECT ','+ENAME FROM EMP E WHERE E.DEPTNO=D.DEPTNO FOR XML PATH('')),1,1,'') FROM EMP D GROUP BY DEPTNO

 SELECT *,(SELECT SUM(SAL) FROM EMP E WHERE E.EMPNO < D.EMPNO) CSUM FROM EMP D


---------------
DECLARE @STR VARCHAR(30)=''
SET @STR='Z'
SELECT @STR='ABL' WHERE 1<0
SELECT @STR

--O/P : Z

-------------------
DECLARE @STR VARCHAR(30)=''
SET @STR='Z'
SELECT @STR='ABL' WHERE 1<0
SELECT @STR

--O/P:ABL

 DIFFERENCE BETWEEN IN AND EXISTS:

During one of my interviews, I faced this question “what is the difference Between IN and EXISTS? Answer: Consider this example I have two tables TableA and TableB, where TableA  contains  Name of an Employees and TableB Contains the address code of the   Employees,both can be joined by ID and ID2.

• IN

1. Returns true if specified value matches any value in the sub query or a list.
2. The sub query will run first and then only outer query.

Example:

SELECT id,
[Name]
FROM dbo.tablea
WHERE id IN (SELECT id
FROM dbo.tableb)

• EXISTS

1. Return true if sub query contain any rows.
2. The Outer query will ran first and then only sub query.

Example:

SELECT id,
[Name]
FROM dbo.tablea AS a
WHERE EXISTS (SELECT id2
FROM dbo.tableb
WHERE id2 = a.id)

Hope everyone like this this article.Please give views and suggestions