How to check if a row exists.

Problem: How to check whether an object, row exists before adding.

Solution: EXISTS

Exists is a logical operator and evaluate either true/false if the sub query, depending on the select statement result set. IF the result set is not empty it raises true, and if the result set is empty it raises false.

Syntax EXISTS (sub query)

EXISTS can be used in the where clause or with IF statement, also NOT EXISTS for the reverse check.

EXISTS can be used to compare data between two tables.

If you want to check for a larger data sets there are better ways to do like merge. etc..

Example:

In this article I am going to show you few examples on how we can use EXISTS.

Example 1: Checking the Information_schema before adding a column on to a table to avoid errors that says column already exists.

So, check is happening by seeing whether a row exists if not then go inside the begin block or else exit the code.

IF NOT EXISTS ( --checking the column exits.

                                SELECT 1

                                FROM INFORMATION_SCHEMA.COLUMNS

                                WHERE table_schema = 'dbo'

                                                AND table_name = 'TableA'

                                                AND Column_name = 'col5'

                                )

BEGIN

                ALTER TABLE dbo.TableA ADD Col5 INT – adding column to a table

END

Example 2:

Let’s write another example, when there is no good join condition but you want to get matching rows in between two tables, we can use Exists clause in the where clause.

In this below example tableA tries to match the values of col1 from tableB and results the values.

SELECT *             

FROM dbo.tableA a

WHERE EXISTS (SELECT *

              FROM dbo.TableB b

              WHERE a.Col1 = b.col1

              );

          --INNER JOIN vs EXISTS--

SELECT *

FROM dbo.tableA a

INNER JOIN dbo.TableB b

              ON a.Col1 = b.col1