mercredi 30 septembre 2015

The logic of WHERE Clause along with > operator and the sub-query

I don't get the logic for the query 3 as below, and hope someone could give me some idea. For the query 3, SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY < 20000);

The sub-query would find out the result where the salary < 20000 first, and that is what query2 shown as below. And then the parent query would find out the result where using all the age's record from the table COMPANY(total of 7 record: 18,19,22,23,24,29,37) to compare with the age's result from sub-query(total of 4 record: 18,19,23,29) and then show the greater record based on age.

I expect the result should only show the ID 7 only like below, since only this record is met the condition. The greater age from the result of sub-query(query 2) is 29, so only this record the age is over 29.


ID          NAME        AGE         SALARY  
7           Vicky       37          32500.0 

Unfortunately my expectation is not met, and it show me the result like query 3 as below.

I hope to understand the logic how its work for query 3, and hope someone could assist.

1.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY;


ID          NAME        AGE         SALARY 
1           John        24          21000.0   
2           Davy        22          20000.0   
3           Kenny       19          9700.0    
4           Henry       23          13555.0   
5           Sam         18          17000.0   
6           Ray         29          8000.0    
7           Vicky       37          32500.0   

2.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE SALARY < 20000;


ID          NAME        AGE         SALARY  
3           Kenny       19          9700.0    
4           Henry       23          13555.0   
5           Sam         18          17000.0   
6           Ray         29          8000.0    

3.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY < 20000);


ID          NAME        AGE         SALARY    
1           John        24          21000.0   
2           Davy        22          20000.0   
4           Henry       23          13555.0   
6           Ray         29          8000.0    
7           Vicky       37          32500.0   

Aucun commentaire:

Enregistrer un commentaire