Use of SQL EXISTS condition with examples.
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 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.
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.