如何用子查询结果替换SQL WHERE子句中的硬编码等值条件？

嘿，我看到你在尝试把SQL里硬编码的1234455换成子查询结果时踩坑了，咱们来快速搞定它～

问题出在哪？ #

你写的where upc.bucket_upc = BucketUPC (select ...)是错误的SQL语法——SQL里没有这种把别名放在等号和子查询之间的写法，另外你最后排序的as应该是笔误，大概率是想写asc（升序）或者desc（降序）。

正确的写法（等值匹配） #

直接把子查询放在=后面就行，但要确保子查询只会返回单个值（一行一列），这样等值匹配才有效：

from model 
join upc on model.bucket_upc_id = upc.bucket_upc_id 
join elect_prop on model.id = elect_prop.id 
where upc.bucket_upc = (
  select bucket.bucket_upc 
  from model as modelInfo 
  join upc as bucket on bucket.bucket_upc_id = modelInfo.bucket_upc_id 
  where modelInfo.id = 179108
) 
order by elect_prop.i_out asc

如果子查询可能返回多个值怎么办？ #

要是你的子查询有可能返回多个bucket_upc值，那就把=换成IN，这样就能匹配所有符合条件的值了：

from model 
join upc on model.bucket_upc_id = upc.bucket_upc_id 
join elect_prop on model.id = elect_prop.id 
where upc.bucket_upc IN (
  select bucket.bucket_upc 
  from model as modelInfo 
  join upc as bucket on bucket.bucket_upc_id = modelInfo.bucket_upc_id 
  where modelInfo.id = 179108
) 
order by elect_prop.i_out asc

另一种思路：用JOIN替代子查询 #

如果你担心子查询的性能（或者只是更喜欢JOIN的写法），可以把获取目标UPC的逻辑做成一个临时表，然后通过JOIN关联，效果是一样的：

from model 
join upc on model.bucket_upc_id = upc.bucket_upc_id 
join elect_prop on model.id = elect_prop.id
join (
  select bucket.bucket_upc 
  from model as modelInfo 
  join upc as bucket on bucket.bucket_upc_id = modelInfo.bucket_upc_id 
  where modelInfo.id = 179108
) as target_upc on upc.bucket_upc = target_upc.bucket_upc
order by elect_prop.i_out asc

内容的提问来源于stack exchange，提问作者prasad