|
If you wish to find rows that have an entry in another table you may write the SQL something like this simplistic example. SELECT customers.customer_no,
customers.customer_name
FROM customers
WHERE customers.customer_no IN (SELECT orders.customer_no FROM orders)
A more efficient way of selecting the same information is to use the EXISTS keyword. SELECT customers.customer_no,
customers.customer_name
FROM customers
WHERE EXISTS (SELECT 1 FROM orders
WHERE orders.customer_no = customers.customer_no)
This is more effecient as exists will stop as soon as the required record is found and makes better use of indexes. You can also use EXISTS when a composite key is involved. This cannot be done using IN without using an expression, which would not use any indexes. SELECT customers.customer_no,
customers.customer_name,
site.site_no,
FROM customers,
site
WHERE site.customer_no = customers.customer_no
AND EXISTS (SELECT 1 FROM orders
WHERE orders.customer_no = customers.customer_no
orders.site_no = customers.site_no)
Note: This example could also of been written with a join to the orders table and a DISTINCT Select. This is not always possible. |