SQL NOT

SQL NOT

The SQL NOT operator is used to invert the result of an expression.

Truth table for SQL NOT:

SELECT *
FROM TableName
WHERE NOT predicate

Table: Employees

EmployeeId	FirstName	LastName	Department	Salary
203	Mazojys	Fxoj	Finance	78000
204	Jozzh	Lnanyo	Finance	45800
205	Syllauu	Dfaafk	Finance	57000
206	Gecrrcc	Srlkrt		62000
302	Jssme	Bdnaa	Development	75000
303	Dnnaao	Errllov	Development	55000
304	Jyoysww	Osk		49000

Example 1: SQL NOT　IN
Select all employees whose last name is not Lnanyo, Srlkrt or Osk:

SELECT *
FROM Employees
WHERE NOT LastName IN ('Lnanyo', 'Srlkrt', 'Osk')

We can also write the statement:

SELECT *
FROM Employees
WHERE LastName NOT IN ('Lnanyo', 'Srlkrt', 'Osk')

The result will look like:

EmployeeId	FirstName	LastName	Department	Salary
203	Mazojys	Fxoj	Finance	78000
205	Syllauu	Dfaafk	Finance	57000
302	Jssme	Bdnaa	Development	75000
303	Dnnaao	Errllov	Development	55000

Example 2: SQL NOT　LIKE
Select all employees whose first name is not start with “J”:

SELECT *
FROM Employees
WHERE NOT FirstName LIKE 'J%'

We can also write the statement:

SELECT *
FROM Employees
WHERE FirstName NOT LIKE 'J%'

The result will look like:

EmployeeId	FirstName	LastName	Department	Salary
203	Mazojys	Fxoj	Finance	78000
205	Syllauu	Dfaafk	Finance	57000
206	Gecrrcc	Srlkrt		62000
303	Dnnaao	Errllov	Development	55000

Example 3: SQL IS NOT NULL
Select all employees who is a member of a department:

SELECT *
FROM Employees
WHERE NOT Department IS NULL

We can also write the statement:

SELECT *
FROM Employees
WHERE Department IS NOT NULL

The result will look like:

EmployeeId	FirstName	LastName	Department	Salary
203	Mazojys	Fxoj	Finance	78000
204	Jozzh	Lnanyo	Finance	45800
205	Syllauu	Dfaafk	Finance	57000
302	Jssme	Bdnaa	Development	75000
303	Dnnaao	Errllov	Development	55000

sqlsteps says:

August 27, 2010 at 5:46 am

manner of description is good.it is also a good site to learn Sql. through this you can improve your sql skills.
life insurance says:

September 14, 2010 at 10:37 am

One thing i want to know that is why NOT operator,NOT IN or NOT EXISTS had been a performance bottleneck in most of the queries? please post