Sunday, November 8, 2015

Not Exists in SQL / How to check if column exists in SQL Server table

Not Exists in SQL Server / How to check if column exists in SQL Server table

Description:

This "Not Exists" keyword we generally used while inserting a new row in table. If that row (depend on keys we passed in where condition) is not exits than insert a row.

Syntax:

IF Not Exists (SQL Query)
Begin
   // Insert statement or do as per requirement. 
End

Example:

Take example of Registration page of Gmail while opening new account. It will check for that particular Email is already exists or not, if not then only it will allow you to create a account else it will ask you to choose another.

IF Not Exists (Select 1 from Users where UserEmail= 'jatin@dotnetaddas.com')
Begin
   // If this user id does not exists then it will come under this block 
  // Create User   
End
Else
Begin
  // User Already Exists.
End

So the sql query we used in above example is,

=>  Select 1 from Users where UserEmail= 'jatin@dotnetaddas.com'

what it will do is, it will check for the email we passed, if exits it will give o/p as '1' else no row will displayed.

The Same way we can make use of only 'Exits' keyword, without not, as per project requirement.

Also we can use this "Exits" and "Not Exists" SQL keywords in Where condition as below,

1) While Delete Operation, 

DELETE From Users
WHERE EXISTS 
(SELECT * FROM User WHERE UserEmail = 'jatin@dotnetaddas.com')

2) While Insert Operation,

INSERT INTO Employee
(Emp_id, Emp_name)
SELECT User_Id, User_name
FROM Users
WHERE EXISTS (SELECT *
              FROM Users
              WHERE WHERE UserEmail = 'jatin@dotnetaddas.com');

 3) While Update Statement,

Update Employee
Set Emp_Name = 
         ( SELECT User_Name FROM Users WHERE WHERE UserEmail = 'jatin@dotnetaddas.com') 
WHERE NOT EXISTS (SELECT *
              FROM Users
              WHERE WHERE UserEmail = 'Jay@dotnetaddas.com');




No comments:

Post a Comment