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



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: