How to Use Not IN operator

Hello Friends

Here i am explaining an example of NOT IN operator.

Suppose you have two tables one is employee and Bademployee
employee and Bademployee has relationship.
Bademployee contains records who are bad

Now you want to show the Good employee so following is the example

Step : 1 create Table emp

Run this Query in your Query Analyzer for table emp

1) CREATE TABLE [dbo].[emp](
[empId] [int] NOT NULL,
[empName] [nvarchar](max) NULL,
CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED
(
[empId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Now

Run this Query in your Query Analyzer for table bademp
CREATE TABLE [dbo].[bademp](
[empid] [int] NOT NULL,
[bademp] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_bademp] PRIMARY KEY CLUSTERED
(
[empid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]




Step 2 :
Now insert the Records in empTable

INSERT INTO emp VALUES (1,'Shubhank')
INSERT INTO emp VALUES (2,'yogesh')
INSERT INTO emp VALUES (3,'firdos')
INSERT INTO emp VALUES (4,'Raghu')
INSERT INTO emp VALUES (5,'shailesh')


Now insert the Records in empTable

INSERT INTO bademp VALUES (5, 'shailesh')


Step 3 : Display Good employee
So run the following Query

SELECT * from emp where
empid NOT IN ( Select empid from bademp)



You will 4 records

Thanks
Shubahnk