sql - 如何修改此sqlite SQL查询，以减少内部联接的筛选？

Question

假设我在sqlite3中有此表name_currency。

name,   currency,   price
AA,     SGD,        1
BB,     USD,        2
CC,     EUR,        3

我需要以这种方式处理价格。

if currency == "SGD", price = price*2 
if currency == "USD", price = price*Y
if currency == "EUR", price = price*Z

Y，Z的值需要从另一个表 currency_multiplier中检索。这就是currency_multiplier的样子。

currency,   rate
SGDUSD,     3    #value of Y
SGDEUR,     4    #value of Z

处理价格列后，所需的输出表将如下所示；

name,   currency,   price
AA,     SGD,        2
BB,     USD,        6
CC,     EUR,        12

这是我为解决此问题而编写的查询。

SELECT name_currency.name, 
       name_currency.currency, 
       name_currency.dividend_yield*currency_multiplier.rate as price, 
from name_currency
INNER JOIN currency_multiplier
    on name_currency.currency = substr(currency_multiplier.currency,4,3)

我编写的查询的输出如下所示；

name,   currency,   price
BB,     USD,        6
CC,     EUR,        12

货币 SGD的行之一丢失。内部联接过滤太多。如何修改查询以解决问题？欢迎提出全新的查询来解决这个问题。

Answer 1

使用左联接代替内联接

SELECT name_currency.name, 
       name_currency.currency, 
       case when currency_multiplier.rate is null then  name_currency.dividend_yield*2 else name_currency.dividend_yield*currency_multiplier.rate end as price, 
from name_currency
left JOIN currency_multiplier
    on name_currency.currency = substr(currency_multiplier.currency,4,3)