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:
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?
- 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)
- We will create a simple query that returns the
customers ID’s :
- The result will be getting just those
customers who don’t have any invoice :
