Stone Coast Web Design Blog

A jQuery plugin for rendering rich, fast-performing photo galleries

http://www.twospy.com/galleriffic/

Here are some examples of using IN vs. EXISTS

--Customers from Spain who made no orders 

SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
 AND NOT EXISTS
    (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID)
SELECT CustomerID, CompanyName 
FROM Customers AS C
WHERE Country = 'Spain'
    AND CustomerID NOT IN(SELECT CustomerID FROM Orders 
                          WHERE CustomerID IS NOT NULL)  

  
--Return customer for whom you cannot find any employee from the USA
--For whom you cannot find any order placed for the subject customer and by the select employee                          

SELECT * FROM Customers AS C
WHERE NOT EXISTS
    (SELECT * FROM Employees AS E
     WHERE Country = 'USA'
    AND NOT EXISTS
        (SELECT * FROM Orders AS O
        WHERE O.CustomerID = C.CustomerID
        AND O.EmployeeID = E.EmployeeID))    

A self-contained subquery is a query that can run independently of the outer query.  Scalar (single-value) subqueries can appear anywhere in the query where an expression resulting in a scalar value is expected.   A scalar subquery is valid when it returns a single value or no values.  However, if a scalar subquery returns more than one value, a run-time eror will occur.

For example, the subquery returns a single value(1) and the outer query returns all orders with employeeID 1. If this subquery were to return more than one record, say (where lastname like 'd%', then it would return an error.

select orderid, employeeid
from orders
where employeeid = (SELECT employeeid from employees
where lastname like N'davolio')

One way to solve this is to use a technique using GROUP BY and DISTINCT COUNT. This query finds all orders with one of the five employeeIDs, groups those orders by CustomerId and returns
CustomerIDs tht have all five distinct employeeID values in their orders.

select customerID from 
orders
where employeeid in (1,2,3,4,8)
group by customerid
having count(distinct employeeid) = 5

But if you don't know the list of employeeID values in advance or there is a large list of them, then this approach is kind of useless.  to make this statement for dynamic and useful, you can use subqueries instead of literals.

select customerID from 
orders
where employeeid in 
(Select employeeID from employees where country = 'usa')
group by customerid
having count(distinct employeeid) = 
(Select Count(*) from employees where Country = 'usa')

I found this interesting. If any of the following field are NULL, then the concatenated value for DirName is also NULL.   You would think that if the less commonly populated fields of Prefix of Suffix were null, dirfname and dirlname would still be returned but this isn’t the case. 

select prefix + ' ' + dirfname + ' ' + dirlname + ' ' + suffix As DirName from individual

To solve this issue, use the IsNull function.

select isnull(prefix, '') + ' ' + dirfname + ' ' + dirlname + ' ' + isnull(suffix, '') As DirName from individual