MYSQL doesn't recognize field when I use RIGHT JOIN, but recognizes when I use NATURAL JOIN

Question

The query executes as expected when I write:

SELECT id, day2.count - day1.count AS diff
FROM day1 NATURAL JOIN day2;

But what I really want is a right join. When I write,

SELECT T1.id, day2.count - day1.count AS diff
FROM day1 RIGHT JOIN day2 AS T1
ON day1.id = day2.id

It says that it can't recognize day2.count in the fields list. (Also, I don't know if it should be RIGHT OUTER JOIN in the second query, but the result is the same in this case.)

I feel like I'm missing something simple.

EDIT: Here are the definitions:

day1

id bigint(8) NOT NULL
count bigint(21) NOT NULL

day2

(Same as day1)

The idea is that there might be new ids in the day2 table that were not there in day1.

Have you tried Natural Right Join? it appears in the mysql reference. http://dev.mysql.com/doc/refman/5.0/en/join.html — jclozano, Apr 22 '13 at 20:20

score 1 · Answer 1 · answered Apr 22 '13 at 19:12

1

You overengineered it. take "as t1" out of your where clause.

answered Apr 22 '13 at 19:12

Dan Bracuk

20,699
4
26
43

And change `T1.id` to `day2.id` to match. – Barmar Apr 22 '13 at 19:13
H'm. I only included T1 because it said "id in fields list is ambiguous" or something like that. – Kaiser Octavius Apr 22 '13 at 19:16
Okay. I guess day2.id makes much more sense. Thanks. – Kaiser Octavius Apr 22 '13 at 19:16
You can use T1, but then you have to use it in the on clause and the select and the where and the group by. In other words, the table is now named t1 and you need to always reference it as such. – HLGEM Apr 22 '13 at 19:45

score 1 · Answer 2 · answered Apr 22 '13 at 19:16

1

Either use the alias name or the table name.

SELECT day2.id, day2.count - day1.count AS diff
FROM day1 RIGHT JOIN day2
ON day1.id = day2.id

answered Apr 22 '13 at 19:16

Slowcoder

2,060
3
16
21

MYSQL doesn't recognize field when I use RIGHT JOIN, but recognizes when I use NATURAL JOIN

2 Answers2