Exists or not exists- that is the answer

Using more complex queries that can answer simple question like who bought product A but didn't bought product B? Or in another Case: which customers from the customer’s table don’t have an order in the order table ,is one of the abilities that we would like to give for the users and allow them to use it simply and freely as we can.

In order to do that we can use in some cases the sub query method which is good and solid, but in some cases using the Exist \ not exist would be better.

And why is that?

There are first of all performance differences that makes the exists operator make a better work of evaluating which is the driving table and by doing so , use in some cases the appropriate index ,there is also the ability to work with nulls.

For a better understanding of the differences between Exists and subquery read here:

http://www.ehow.com/info_8750242_differences-between-exists-sql.html

http://www.techrepublic.com/article/oracle-tip-understand-the-difference-between-in-and-exists-in-subqueries/5297080

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Using the Island resort we will use the Customer and the Sales tables in order to answer a simple question:

Who are the customers who haven’t paid so far?

1. first we will build the following predefined condition using this syntax:

SELECT Customer.cust_id
FROM Customer

WHERE NOT EXISTS

(select Sales.cust_id from Sale where Customer.cust_id=Sales.cust_id)

2. We will create a simple query that returns the customers ID’s :

3. The result will be getting just those customers who don’t have any invoice :