A friend of mine set us a challenge to keep our grey cells from atrophy:
The Tables:
person
| person_id | name |
|---|---|
| 1 | Dan |
| 2 | Chris |
| 3 | Rafa |
| 4 | Jose |
| 5 | The Queen |
person_car_colour
| person_id | name |
|---|---|
| 1 | Blue |
| 2 | Red |
| 3 | Red |
| 4 | Blue |
| 4 | White |
| 5 | Red |
| 5 | White |
| 5 | Blue |
And the questions –>
Find in a single statement:
1. who has a red car and a white car?
2. who has a red car, a white car, but no blue car?
Well it is possible to answer both these questions with or without using Correlated subqueries.
The following answers work using Sybase ASE:
Correlated Subqueries
Who has red and white cars?
SELECT *
FROM person p
WHERE EXISTS ( SELECT 1
FROM person_car_colour pcc
WHERE pcc.person_id = p.person_id
AND pcc.colour = 'Red')
AND EXISTS ( SELECT 1
FROM person_car_colour pcc
WHERE pcc.person_id = p.person_id
AND pcc.colour = 'White')
Who has red, white but no blue cars?
SELECT *
FROM person p
WHERE EXISTS ( SELECT 1
FROM person_car_colour pcc
WHERE pcc.person_id = p.person_id
AND pcc.colour = 'Red')
AND EXISTS ( SELECT 1
FROM person_car_colour pcc
WHERE pcc.person_id = p.person_id
AND pcc.colour = 'White')
AND NOT EXISTS ( SELECT 1
FROM person_car_colour pcc
WHERE pcc.person_id = p.person_id
AND pcc.colour = 'Blue')
The solutions without subqueries
Who has red and white cars?
person_car_colour c1,
person_car_colour c2
WHERE c1.person_id = p.person_id
AND c2.person_id = p.person_id
AND c1.colour = 'Red'
AND c2.colour = 'White'
Who has red, white but no blue cars?
person p
INNER JOIN person_car_colour c1 ON p.person_id = c1.person_id
INNER JOIN person_car_colour c2 ON p.person_id = c2.person_id
LEFT JOIN person_car_colour c3 ON p.person_id = c3.person_id
AND c3.colour = 'Blue'
WHERE c1.colour = 'Red'
AND c2.colour = 'White'
AND c3.colour = NULL
The example above also shows how you can use ANSI sql joins to select only rows with null values after an outer join using Sybase. This stumped us for quite a while, I don’t think it is possible using TransactSQL only.
The differences between the query styles above may not be just a question of taste. If your database server does not optimise out the subqueries, and ends up running the subquery once for each row in the outer select, then performance will go down the pan. I am not sure whether Sybase does the optimisation – but I’d rather not risk it!
Quote from the above link:
A correlated subquery is a nested select that refers to a column from the outer select. Correlated subqueries are often considered evil because the inner result set must be constructed for every single row that is a candidate for inclusion in the outer result set.
