0

I have a MySQL query which I think would throw an exception, but it did not.Just want to figure out why? The query is :

select count(distinct uid) 
from nov_visit_20161201 
where pv_listen>0
and uid in (select distinct uid from nov_visit_20161120 where pv_listen>0);

the problem is table nov_visit_20161120 doesn't have a pv_listen column,but the query worked well. When I run the subquery alone: 

select distinct uid from nov_visit_20161120 where pv_listen>0

I got this : "Unknown column 'pv_listen' in 'where clause'".

Anyone can tell me why?

madtracy
  • 931
  • 6
  • 6
  • Your Outer query refers to another table and inner to another and the table which inner one refers to does not have that column. That is why one works and one doesn't – Hanky Panky Dec 13 '16 at 07:50
  • 2
    When that table does not have the column you are referencing and you know it then what's the question? – Hanky Panky Dec 13 '16 at 07:50
  • @Hanky Panky, I think there should be an error,but no, and I want to know why.@Sumit Gupta's answer is correct. – madtracy Dec 13 '16 at 08:56

2 Answers2

1

I have observe the same things few times, the explanation can be, that pv_listen in sub query is referring to main query table.

consider a situation where you want to run a sub query that use the value from main query column, how you write it? you simply put the name of column in sub query and data row's column value is used in there. So, when a column is not found in Sub query table/dataset it consider the column belong to main query, and in your case it found the same. hence no error.

So, it is actually running it as 

select count(distinct uid) 
from nov_visit_20161201 
where pv_listen>0
and uid in (select distinct uid from nov_visit_20161120);

As the filter in sub query do not filter any record.

Sumit Gupta
  • 2,152
  • 4
  • 29
  • 46
0

If the column doesn't exist in the table, just remove it from the where condition:

select count(distinct uid) 
from nov_visit_20161201 
where pv_listen>0
and uid in (select distinct uid from nov_visit_20161120);
Bram Verstraten
  • 1,414
  • 11
  • 24
  • In my case,I just need add the pv_listen column in table nov_visit_20161120,I just want to find out why.@Sumit Gupta's answer is correct. – madtracy Dec 13 '16 at 08:51