Scenario: a table where the same object appears multiple times, each time with different values on certain attributes, as in “cities located on two different seas” or “cnames with both current and deposit accounts”.

Let’s crank a few examples given the following schema:

account(id, type, cname)

The very straightforward way:

SELECT cname FROM account WHERE type='current'
INTERSECT
SELECT cname FROM account WHERE type='deposit'

Sadly, MySQL does not support INTERSECT, but restructuring the same query using IN will suffice.

The clean way:

SELECT cname
FROM account
GROUP BY cname
HAVING COUNT(DISTINCT type) > 1

Clean + Control:

SELECT cname
FROM account
WHERE type IN ('this', 'that')
GROUP BY cname
HAVING COUNT(DISTINCT type) > 1

Using cases:

SELECT cname FROM account 
GROUP BY cname  
HAVING SUM(CASE WHEN type = 'current' THEN 1 END) > 0 
AND SUM(CASE WHEN type = 'deposit' THEN 1 END) > 0

Using self joined tables:

SELECT DISTINCT a1.cname 
FROM account a1 JOIN account a2 ON a1.cname = a2.cname 
WHERE a1.type <> a2.type;

A more interesting case is given by a query such as the cnames of customers with every type of account”, assuming we have no idea which type of accounts/how many of them there are.

SELECT cname FROM account 
GROUP BY cname 
HAVING COUNT(DISTINCT type) = 
    (SELECT COUNT(DISTINCT type) FROM account);

A kind of brain-fucking, inefficient version of the same query is as follows:

SELECT cname, type
FROM account AS cust_account 
WHERE NOT EXISTS ( 
    SELECT type 
    FROM account 
    EXCEPT
    SELECT type 
    FROM account 
    WHERE account.cname = cust_account.cname
);

This query for every row in cust_account will:
1. get a list with all the types in account
2. get a list with all the types in account that the current cust_account.cname has, and subtract the list from (1)
3. if list(1) – list(2) returns the empty set it means the current cust_account.cname has all the types in the table.