Use of exists condition

The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.


SELECT columns
FROM tables
WHERE EXISTS ( subquery )

Using with Select statement:



SELECT *
FROM table1
WHERE EXISTS
  (select *
    from table2)
    where table1.id = table2.id)

This select statement will return all records from the table1 where there is at least one record in the table2 with the same id.

Another example which is used with Delete statement given below:


DELETE FROM table1
WHERE EXISTS
  (select *
    from table2
    where table1.id = table2.id)

Comments

Author: Phagu Mahato11 Feb 2014 Member Level: Gold Points : 3

SQL Statements that use the SQL EXISTS Condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table


UPDATE Product SET supplier_name = (SELECT customers.name  FROM customers WHERE customers.customer_id = Product.supplier_id)
WHERE EXISTS (SELECT customers.name
              FROM customers
              WHERE customers.customer_id = Product.supplier_id);

Submit Article

Return to Article Index

Use of exists condition

Comments

Email subscription

About dotnetspider.com

Quick Links

General

Services