CODEWITHSUNDEEP
mysqljoin
nikel
nikel

Reputation: 3564

Nested Where clause can access global variable but inner join can't

The below example is just a demonstration of the problem i am facing.

I have two tables A & B on which i want to query. There schemas are as below 

create table A
(
    id int,
    B_id int, 
    D_id int,
    created date
);


create table B
(
    id int,
    C_id int
);

Table A can have multiple rows for a given B_id.

I insert test data as below :

insert into A(id, B_id, D_id, created) values(2, 1, 0, now());
insert into A(id, B_id, D_id, created) values(3, 1, 0, now());

Now, I want to fetch the newest(whose created is having the highest value) rows in A which have B_id = 1

Now, the problem :

I tried below double inner join which did not work 

select * from A 
    inner join B on A.B_id = B.id
    inner join ( select * from A  where A.B_id = B.id order by created desc limit 1) as A1 on A.id = A1.id
    and A.B_id = 1;

this fails with the error "Unknown column 'B.id' in 'where clause'"

However if i replace the second inner join with a where clause as below, it works :

select * from A 
    inner join B on A.B_id = B.id
    and A.id =  ( select id from A  where A.B_id = B.id order by created desc limit 1) 
    and A.B_id = 1;

Why can where clause access the B.id in global scope but inner join can't??

Upvotes: 1

Views: 550

Answers (1)

Tin
Tin

Reputation: 804

This is a good point. It took me a while to get my head around. First of all I personally would not call it "global" scope. But I've got your point :)

Here is how I understand it. Please correct me if I'm wrong.

First query: I changed your query B.id to 1, so I can run the query correctly. I changed it to the following:

select * from A 
    inner join B on A.B_id = B.id
    inner join ( select * from A  where A.B_id = 1 order by created desc limit 1) as A1 on A.id = A1.id
    and A.B_id = 1;

After I changed it, I did explain select ... to see how it work. Here is what I've got.

id   select_type    table         type     possible_keys     key     key_len     ref     rows     Extra
--------------------------------------------------------------------------------------------------------
1    PRIMARY        <derived2>    system   null              null    null        null    1        null
1    PRIMARY        B             ALL      null              null    null        null    1        Using where
1    PRIMARY        A             ALL      null              null    null        null    4        Using where; Using join buffer (Block Nested Loop)
2    DERIVED        A             ALL      null              null    null        null    4        Using where; Using filesort

It seems that your subquery select * from A where A.B_id = 1 order by created desc limit 1 is executed during or before INNER JOIN. B.id is not yet available since INNER JOIN hasn't been done yet.

Second query: I did the same explain select ...

id   select_type        table     type  possible_keys     key     key_len     ref     rows     Extra
----------------------------------------------------------------------------------------------------
1    PRIMARY            B         ALL   null              null    null        null    1        Using where
1    PRIMARY            A         ALL   null              null    null        null    4        Using where; Using join buffer (Block Nested Loop)
2    DEPENDENT SUBQUERY A         ALL   null              null    null        null    4        Using where; Using filesort

As you can see, your subquery is executed after INNER JOIN. Therefore B.id is available.

Upvotes: 1

Related Questions