SQL1:

 SELECT NAME, 
(SELECT SEXNAME FROM TBL_SEX B WHERE A.S EXNO=B.S EXNO) AS SEXNAME 
The FROM TBL_PERSON A 
WHERE CERTNO='3404031'; 

SQL2:

 SELECT NAME, A.S EXNAME 
The FROM TBL_PERSON A 
LEFT the JOIN TBL_SEX B ON A.S EXNO=B.S EXNO 
WHERE CERTNO='3404031'; 

These two writing performance, which way is more high, why?

CodePudding user response:

SQL2: there is a mistake, the following amended

 SELECT NAME, B.S EXNAME 
The FROM TBL_PERSON A 
LEFT the JOIN TBL_SEX B ON A.S EXNO=B.S EXNO 
WHERE CERTNO='3404031'; 

CodePudding user response:

First, a scalar subquery, connection mode of execution plan, affirmation is NL,

The second, left connection, the connection of the execution plan could be MJ or HJ,

You look at the first, the characteristics of the three connection ways, there will be a conclusion,

CodePudding user response:

First to make sure that in the SQL1 b.S EXNO only a record, not an error;
Personal experience if B.S EXNO index, SQL1 query (child) hurry up,
Almost no index, two

CodePudding user response:

The second left connection efficiency better.

CodePudding user response:

Use is not good, the efficiency of the scalar subquery is no lower limit,

CodePudding user response:

Simple say, outer query data volume is larger, the performance of the scalar subquery is worse, written more than even to complete the connection,