Oracle SQL多表连接查询：筛选澳大利亚特定类型玩家信息

需求 #

获取来自澳大利亚、玩家类型为NEW或EXE的玩家的全名、电子邮箱、玩家编号及COUNTRY_ID。

初始错误SQL语句 #

SELECT T.FIRST_NAME, T.LAST_NAME, E.EMAIL_ID, PN.PLAYER, C.COUNTRY_ID
FROM PLAYER_TYPE T
LEFT JOIN PLAYER_CONTACT C
ON T.PLAYER_ID = C.PLAYER_ID
RIGHT JOIN PLAYER_CONTACT C
ON P.CONTACT_ID = C.CONTACT_ID
AND C.COUNTRY_CODE='AUS'
AND T.PLAYER_TYPE = 'NEW' OR 'EXE'

初始语句的问题 #

• 重复使用表别名C关联PLAYER_CONTACT表，造成别名冲突
• 引用了未定义的表别名P和E
• 逻辑判断错误：T.PLAYER_TYPE = 'NEW' OR 'EXE'的语法不符合需求，会被错误解析，正确逻辑应为指定字段匹配多个值
• 连接逻辑冗余混乱，无需同时使用LEFT JOIN和RIGHT JOIN，需求仅需筛选符合条件的记录

修正后的SQL语句 #

SELECT 
    T.FIRST_NAME || ' ' || T.LAST_NAME AS FULL_NAME,
    PC.EMAIL_ID,
    T.PLAYER_ID AS PLAYER,
    C.COUNTRY_ID
FROM PLAYER_TYPE T
JOIN PLAYER_CONTACT PC ON T.PLAYER_ID = PC.PLAYER_ID
JOIN COUNTRY C ON PC.COUNTRY_CODE = C.COUNTRY_CODE
WHERE 
    C.COUNTRY_CODE = 'AUS'
    AND T.PLAYER_TYPE IN ('NEW', 'EXE')
ORDER BY T.PLAYER_ID;

预期输出结果 #

FULL_NAME       EMAIL_ID           PLAYER COUNTRY_ID
--------------- ------------------ ------ ----------
MARK CLARKSON   dfgh@gmail.com     480    04
CATH SPEARS     tygh@yahoo.com     481    04
FESS LOPEZ      fgvhb@yandex.com   482    04
FEXS LOPEZ      byvg@google.com    483    04
EOVA SMITH      ghjkjh@sdf.com     493    04

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