Now, SQL for

 select m. *, d. * from view_ordertracking m 
Left the join view_majorplanprocess d 
On m.o rderno=d.o rderno 
And m.o rderLineNum=d.o rderlineno 

, which m and d are extremely complex view, m table data volume of high efficiency and less d table data volume efficiency is low, but because it is left the join, the driver table is d, therefore the entire SQL is slow,
Therefore, I try to inner join, found soon, so want to continue along this road continue to optimize, changed the inner join + union, found that results in, don't know why (m, d are no duplicate data), after improvement of SQL

 select m. *, d. * from view_ordertracking m 
Left the join view_majorplanprocess d 
On m.o rderno=d.o rderno 
And m.o rderLineNum=d.o rderlineno 
Union all 
The select m. *, a lot of null from view_ordertracking m 

Why two SQL data in, left the join is not inner join + left table data?

CodePudding user response:

After changing inner join inequitable;

Suggested that the view, instead of materialized views;

CodePudding user response:

reference 1st floor wmxcn2000 response:

to inner join after inequitable;

Suggested that the view, instead of materialized views;

I change to inner join, and with the union repair back

CodePudding user response:

 select m. *, d. * from view_ordertracking m 
Innerjoin view_majorplanprocess d 
On m.o rderno=d.o rderno 
And m.o rderLineNum=d.o rderlineno 
Union all 
The select m. *, a lot of null from view_ordertracking m 

the second period of SQL is wrong, now I attached,