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');