Is the following join sentence valid?

Is this join valid in MySQL:

FROM a
JOIN b on a.c = b.c an开发者_如何学God b.c = 1

Well it does work like this but when I use a RIGHT JOIN the b.c = 1 is ignored. Why is that?

---

It isn't ignored in a RIGHT JOIN.

(Conceptually) The Join happens with the filter applied then any non matching rows from b get added back in.

So the query

SELECT a.c, b.c
FROM   (select 1 AS c
        UNION ALL
        SELECT 2) a
       RIGHT JOIN (select 1 AS c
                   UNION ALL
                   select 2 AS c) b
         on a.c = b.c
            and b.c = 1  

Returns

c           c
----------- -----------
1           1
NULL        2

You can see that despite the fact both tables have a 2 the additional filter in the join clause means that these rows don't get joined. However as it is a right outer join the 2 row from the right hand table (b) still appears in the results.

Edit: RE: Question in comments

Then how do I LEFT JOIN without adding the filtered rows back in? If I use a WHERE clause instead the join will be slower because more rows will have to be joined, right?

I don't really understand this question.

You would choose LEFT vs RIGHT join on the basis of whether you wanted to preserve all rows from the left input or the right input. For a simple 2 table query you can of course use either and just reverse the order of the tables.

Similarly for the issue of whether to put the filter in the WHERE clause or the JOIN condition. In an OUTER JOIN this can change the results (In my example data moving b.c = 1 to the WHERE clause means that only 1 row will be returned not 2)

For the case of INNER JOIN it does not matter where the filters are placed but I find it more sensible to put filters concerned with the relationship between the 2 tables in the JOIN and all others in the WHERE

---

Yes. It's OK to have extra conditions in the on clause that have nothing to do with other tables

---

Yes it is valid.

However when you use a right join, you will effectively get all the values in the "right" table that doesn't satisfy the join condition in addition to those who do. That's what right join does

Use a where clause to restrict what you pull out of table b in that case.

FROM a RIGHT JOIN b on a.c = b.c WHERE b.c = 1

---

Why not write it like this to make it clearer:

FROM a
JOIN b on a.c = b.c
WHERE b.c = 1