SQL: EXISTS CONDITION

Use of SQL EXISTS condition with  examples.

DESCRIPTION

SQL EXISTS condition is used for combination with  sub query and it is considered to be met the condition, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

Syntax for SQL EXISTS condition is:

WHERE EXISTS ( subquery );

subquery is a SELECT statement.

EXAMPLE - WITH SELECT STATEMENT

Start with simple example.

SQL SELECT statement that uses the SQL EXISTS condition:

SELECT Amount FROM Salary WHERE  Employee_Id EXISTS (SELECT  employee_Id   FROM employee  WHERE designation=’Manager’);

SQL Exist will return the employee_id and matching the employee_id relation with the salary will return the amount.

EXAMPLE - WITH SELECT STATEMENT USING NOT EXISTS

EXISTS condition can also be combined with the NOT operator.

For example,

SELECT * FROM suppliers WHERE NOT EXISTS (SELECT *  FROM orders WHERE suppliers.supplier_id = orders.supplier_id);

SQL EXISTS example will return all records from the suppliers table where there are no records in the orders table for the given supplier_id.