EXISTS Condition

SQL: EXISTS Condition

     <hr>         <p>The EXISTS condition is considered "to be met" if the subquery returns at least one row.</p>         <p>The syntax for the EXISTS condition is:</p>         <blockquote>           <p>SELECT columns<br>             FROM tables<br>             WHERE EXISTS ( subquery );</p>         </blockquote>         <p>The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.</p>         <br>         <p class="example_heading">Example #1</p>         <p>Let's take a look at a simple example. The following is an SQL statement that uses the EXISTS condition:</p>         <blockquote>           <p>SELECT *<br>             FROM suppliers<br>             WHERE EXISTS<br>   (select *<br>     from orders<br>     where suppliers.supplier_id = orders.supplier_id);</p>         </blockquote>         <p>This select statement will return all records from the suppliers table where there is at least one record in the orders table with the same supplier_id.</p>         <br>         <p class="example_heading">Example #2 - NOT EXISTS</p>         <p>The EXISTS condition can also be combined with the NOT operator.</p>         <p>For example,</p>         <blockquote>           <p>SELECT *<br>             FROM suppliers<br>             WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);</p>         </blockquote>         <p>This will return all records from the suppliers table where there are <strong>no</strong> records in the <em>orders</em> table for the given supplier_id.</p>         <br>         <p class="example_heading">Example #3 - DELETE Statement</p>         <p>The following is an example of a delete statement that utilizes the EXISTS condition:</p>         <blockquote>           <p>DELETE FROM suppliers<br>             WHERE EXISTS<br>   (select *<br>     from orders<br>     where suppliers.supplier_id = orders.supplier_id);</p>         </blockquote>         <br>         <p class="example_heading">Example #4 - UPDATE Statement</p>         <p>The following is an example of an update statement that utilizes the EXISTS condition:</p>         <blockquote>           <table border="0" cellspacing="0" cellpadding="0" width="476" class="sql_command">             <tbody>
UPDATE suppliers   
         <tr class="tr_left_top">               <td width="136">SET supplier_name =</td>               <td width="337">( SELECT customers.name<br>                 FROM customers<br>                 WHERE customers.customer_id = suppliers.supplier_id)</td>             </tr>             <tr class="tr_left_top">               <td colspan="2" width="473">WHERE EXISTS<br>   ( SELECT customers.name<br>     FROM customers<br>     WHERE customers.customer_id = suppliers.supplier_id);</td>             </tr>           </tbody>

</table> </blockquote>
<p class="example_heading">Example #5 - INSERT Statement</p> <p>The following is an example of an insert statement that utilizes the EXISTS condition:</p> <blockquote> <p>INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM suppliers
WHERE exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);</p> </blockquote>