The IN clause onlty extract those values that match or exist in the database against the values which are being given in the IN clause but it does not diplay the values that do not meet the condition not even with a null values that is why some time we need to make a temporary table to stroe these values and then they are extracted using left joins so that all the values should be visible.
1. SELECT EMPNO, LASTNAME, SALARY
FROM EMP
WHERE WORKDEPT IN ('D11', 'D21', 'E11')
2.SELECT *
FRROM EMPLOYEE
WHERE COUNTRY IN ('PAKISTAN','UK','US','AUTRALIA' )
NOT INTo select employees that has not been hired in the month of MAY 2010
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE NOT BETWENN '2010-05-01 00:00:00' AND '2010-05-31 23:59:59'
Not IN is the revers of IN clause, it is advised if there are only few values then it is fine else avoid using NOT IN becuase it effect your performance istead use EXIST or NOT EXIST clause it is much faster then the IN or NOT IN
2.SELECT *
FRROM EMPLOYEE
WHERE COUNTRY NOT IN ('PAKISTAN','UK','US','AUTRALIA' )
NOT BETWEEN
0 comments:
Post a Comment