MySQL online some hidden trouble query sql

Development write a few statements, think the query results and logic a bit inconsistent, get here together to analyze the next.

The statements are as follows:

SelectTp.title, Tp.amount,ifnull (sum( Case  whenTu.type= 1  ThenTi.invest_amountElse 0 End),0) asAinvestamount,ifnull (sum( Case  whenTu.type= 2  ThenTi.invest_amountElse 0 End),0) asBinvestamount,ifnull (sum( Case  whenTu.type= 3  ThenTi.invest_amountElse 0 End),0) asCinvestamount fromT_invest TiJoinT_user Tu onTi.user_id =Tu.idJoinT_project TP onti.project_id=tp.idwhereTp.id= '48346631623950333337353439383060';

There are id:48346631623950333337353439383060 in T_project, but there is no such project_id in T_invest. So the statement should theoretically have no output, but actually output the following result:

In order to illustrate this problem, we will establish the following form and data.

Mysql> Select *  fromT1;+----+-------+|Id|Name|+----+-------+|  3 |Chen||  1 |Zhang|+----+-------+2Rowsinch Set(0.00sec) MySQL> Select *  fromT2;+------+--------+|Id|Course|+------+--------+|    2 |Math|+------+--------+1Rowinch Set(0.00sec) then execute the following statement 1:mysql> SelectT1.id,t1.name,t2.id,t2.course,sum(t1.id) fromT1JoinT2 onT1.id=T2.idwhereT1.id=1 ;+----+-------+------+--------+------------+|Id|Name|Id|Course| sum(t1.id)|+----+-------+------+--------+------------+|  1 |Zhang| NULL | NULL   |       NULL |+----+-------+------+--------+------------+1Rowinch Set(0.00Sec

The discovery unexpectedly has the output, then executes the following statement 2:

Mysql> SelectT1.id,t1.name,t2.id,t2.course,sum(t1.id) fromT1JoinT2 onT1.id=T2.idwhereT1.idinch(1,NULL) ;+----+------+------+--------+------------+|Id|Name|Id|Course| sum(t1.id)|+----+------+------+--------+------------+| NULL | NULL | NULL | NULL   |       NULL |+----+------+------+--------+------------+1Rowinch Set(0.00Sec

Found to be all null.

Execute the following statement 3, returning the empty set.

MySQL>Select  t1.id,t1.name,t2.id,t2.course,sumfromjoin  on t1.id=where t1.id=1Group  by Set (0.00 sec)

The syntax in MySQL is not particularly strict, and statement 1 and statement 2 actually report syntax checking in Oracle. Report: ORA-00937: Not a single group of grouping functions

General use of aggregate functions generally with the group by will be more strict, and out of such a situation only when there is a join time out, a single table query is not a problem, the Union Table query aggregation function is recommended to use the wording of statement 3.

Some hidden trouble on MySQL line query SQL