Question

I m not that good in SQL and I ve come across a problem I don t know how to solve. I ve read and re-read parts of a book about SQL (O Reilly s Learning SQL) which I hoped would contain the information I needed but I haven t found it.

My problem is the following. I ll use a simplified example to make it easier to discuss.

I ve got three tables, car, rim and the combination of the two: carRim.

car
    carId
    description

rim
    rimId
    description

carRim
    carRimId
    carId
    rimId
    price

In the table carRim I have an extra attribute of price, because the price of a rim is potentially different for every type of car. A constraint I have is that every type of rim should only be coupled once to every type of car. So all combinations of car-rim should be unique.

If I want to add a rim to a car I need a list of rims that are not yet coupled to that car. For this I think I need the rim table and the carRim table respectively for the total list of rims and the list of carRims that are already coupled to the car I want to add a rim to.

I ve written the (simple) query to make the list of rims that are coupled to a specific car, in the following example the car with carId 9.

SELECT
    * 
FROM
    rims 
INNER JOIN
    carRims 
ON
    carRims.rimId = rim.rimId 
WHERE
    carRims.carId = 9

But now I need the list of rims that are not yet coupled to a specific car. The problem is that if I do a LEFT OUTER JOIN the list I get is "tainted" with couplings of rims to other cars, so the filter condition of "WHERE carRims.carId IS NULL" does not work.

SELECT
    * 
FROM
    rims 
LEFT OUTER JOIN
    carRims 
ON
    carRims.rimId = rim.rimId 
WHERE
    carRims.carId IS NULL

Another challenge is that I can t use any syntax new to MySQL 5, like subqueries, because my client is using MySQL 4 and can t upgrade at this time.

Can a query for this problem be written in MySQL 4, I suspect it can.

Thanks!

Answer 1

You can put extra conditions in a left outer join ON expression. What you want is a row from carRims that matches your rims row, and belongs to car number 9, right?

SELECT
    * 
FROM
    rims 
LEFT OUTER JOIN
    carRims 
ON
    carRims.rimId = rim.rimId 
    AND carRims.carId = 9
WHERE
    carRims.carId IS NULL

If there are other carRims for other cars, they ll be filtered out by the extra condition in the ON clause.

Re your questions about where to put a condition, in the JOIN or in the WHERE clause:

For outer joins, it matters where you put the comparison. The condition in a JOIN clause is for testing whether rows in one table match rows in the other table. What we do with the rows from the respective tables after we test the match depends on the type of join.

For an outer join, we want rows from rims even if there s no matching row in carRims. What if we were to put the carID=9 condition in the WHERE clause?

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID
WHERE c.carID = 9

Here s what happens: the outer join returns all rows from rims, with rows from carRims that match the rimID and includes the rows with spurious carID values. Only if none of the cars match a given rim does it use NULL for the c.* columns.

But then the WHERE clause eliminates all the rows resulting from the join, unless carID is the value 9. That means it also eliminates where carID is NULL, that is it eliminates rows for any rim matches none of the cars. Therefore the result becomes equivalent to the result of an INNER JOIN.

So we need to exclude rows from carRims with the wrong carID before the rows are joined to rims.

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID AND c.carID = 9

It s a condition on what rows from that individual table can match rows in the other joined table.

Many books say you can freely mix conditions between the ON clause and the WHERE clause. But this isn t true in all cases. It works for INNER JOIN, because the end result is the same either way. It also works for a condition that applies only to the left table in a left join, for example:

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID AND r.make =  ABC Rims 

FROM rims r LEFT OUTER JOIN carRims c ON r.rimId = c.rimID 
WHERE r.make =  ABC Rims

For a condition on the right table in a left outer join, it does matter where you put the condition.

One more comment on something you said:

A constraint I have is that every type of rim should only be coupled once to every type of car. So all combinations of car-rim should be unique.

So have you declared a UNIQUE constraint on carRims(carId,rimId)?

Answer 2

SELECT  *
FROM    rims r
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    carRims cr
        WHERE   cr.rimId = r.RimId
                AND cr.carID = 9
        )

Update:

To rewrite the NOT EXISTS into a LEFT JOIN / IS NULL, you ll need to put all conditions into the ON clause of the join:

SELECT  r.* 
FROM    rims r
LEFT OUTER JOIN
        carRims cr 
ON      cr.rimId = r.rimId 
        AND cr.crID = 9
WHERE   cr.carId IS NULL

Answer 3

(I needed some more room to elaborate, a comment did not have enough characters)

@Bill Karwin The query you ve provided is the correct answer to my problem. Thanks!

What I wanted was a list of rows (rims) that are not connected to a specific car.

In the "Learning SQL" book I read that the location of filter and join conditions in the query did not matter. They gave an example of an inner join where there was 1 join condition (after the ON clause) and 1 filter condition (after the WHERE clause). They demonstrated that whether you put those two conditions after the ON clause or after the WHERE clause effectively did not matter.

With that in mind I don t understand a part of your solution. If the condition

carRims.carId = 9

and the condition

carRims.carId IS NULL

are both present, doesn t that result in an empty resultset? Because no field can be 9 and NULL simultaneously right?

My suspicion then is: the conditions in the JOIN clause are location-bound, and you can t just put them in the WHERE clause.

Is this correct?

友情链接