MYSQL自联结

本文主要是介绍MYSQL自联结，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

查找：生产ID为DTNTR物品的供应商，生产的其他物品。

子查询

SELECTprod_id,prod_name 
FROMproducts 
WHEREvend_id = ( SELECT vend_id FROM products WHERE prod_id = 'DTNTR' );

自联结

SELECT p1.prod_id,p1.prod_name   # 必须指定为p1.prod_id,p1.prod_name
FROMproducts AS p1,products AS p2 
WHEREp1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

p2已经被限制为了具体的一行

SELECT p2.prod_id,p2.prod_name
FROMproducts AS p1,products AS p2 
WHEREp1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

SELECT* 
FROMproducts AS p1,products AS p2 
WHEREp1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'

比较

从运行时间看，自联结比子查询快！

这篇关于MYSQL自联结的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---