本文发表在 rolia.net 枫下论坛My very first solution was same as cloud's. Because logically it is the clearest way to get the work done. My self join answer, in fact, was just a variation of cloud's answer by removing the sub-query. Among the answers with sub-query, apparently cyt's is the best.
I share with abba's view completely. The question I posted here was way much simplified from the real life problem I faced. In real life, I needed to join it with something like other 6 or 7 tables using, for instance, the value of B. Some of those tables are huge, and with different index etc. Using sub-query as part of the complex query risks the chance to be benefit from the index in tables, and may result horrible performance. The original SQL for the original problem was one page long. Therefor in the answer I posted here, the highest priority in my mind was removing any sub-queries, rather than the fastest performance of querying this single table. In this sense, abba is absolutely correct.
Thank you for your excellent execution plan, onewayticket! As you can tell I am not a dba, but I got your idea. I definitely learned something.
To answer hongri's question, you cannot change T1.a=T2.a to T1.b=T2.b. Your query looked like working but indeed it didn't because the original table happened to have the values in B column unique. Change your table by making B column to 'B1' for all the rows, your will find your query doesn't work.
It has been fun to share ideas with you folks. Let's keep going!更多精彩文章及讨论，请光临枫下论坛 rolia.net
I share with abba's view completely. The question I posted here was way much simplified from the real life problem I faced. In real life, I needed to join it with something like other 6 or 7 tables using, for instance, the value of B. Some of those tables are huge, and with different index etc. Using sub-query as part of the complex query risks the chance to be benefit from the index in tables, and may result horrible performance. The original SQL for the original problem was one page long. Therefor in the answer I posted here, the highest priority in my mind was removing any sub-queries, rather than the fastest performance of querying this single table. In this sense, abba is absolutely correct.
Thank you for your excellent execution plan, onewayticket! As you can tell I am not a dba, but I got your idea. I definitely learned something.
To answer hongri's question, you cannot change T1.a=T2.a to T1.b=T2.b. Your query looked like working but indeed it didn't because the original table happened to have the values in B column unique. Change your table by making B column to 'B1' for all the rows, your will find your query doesn't work.
It has been fun to share ideas with you folks. Let's keep going!更多精彩文章及讨论，请光临枫下论坛 rolia.net