Exploring the synergistic relationship between conventional water (CW) and unconventional water (UW) is a better way to alleviate water scarcity. However, research remains lacking on the complex relationship between CW and UW from a water resources subdivision perspective. By constructing a heterogeneous trans-log stochastic frontier model, this study estimated the technical efficiency, output elasticity, and substitution elasticity; further, the study innovatively explored five development pathways for CW and UW. The results are as follows. First, China's overall technical efficiency demonstrated a fluctuating upward trend over the study period, which was significantly boosted by higher levels of research and development investment and an increased number of granted patents. Second, compared with UW, CW made a more substantial contribution to output. The output elasticity of CW demonstrated a “low in the center, high on the periphery” pattern, with the lowest elasticity areas clustered in the middle and lower reaches of the Yellow River. Conversely, the output elasticity of UW followed a “high in the east and low in the west” pattern, which was similar to that of capital. Third, with 2012 as the turning point, CW and UW shifted from a substitution to a complementary relationship. Fourth, this study proposes alternative structural optimization paths under various scenarios, namely balanced development of CW and UW, CW-driven, UW-driven, coordinated development of CW and UW, and aided development of CW and UW.