如果这些语句可以用SELECT a FROM x,y JOIN z ON y.id = z.id WHERE ...表示 更改 x 和 y 的顺序将起作用，或者导致错误。下面是两个完整的查询和 SQL 开发人员中产生的确切错误
示例 1：
(片段(FROM (SELECT ...), persons prsn LEFT OUTER JOIN point_summaries psum ON psum.prsn_id prsn.prsn_id
SELECT prsn.prsn_id, person_points.points, psum.psum_points_available
FROM (SELECT prsn_id prsn_id, sum(points) points
FROM (SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
invoices inv,
transaction_details tdet,
orders ord,
persons prsn
WHERE pnts.pnts_status = 'R'
AND tdet.tdet_id = pnts.tdet_id
AND tdet.inv_id = inv.inv_id
AND inv.ord_id = ord.ord_id
AND ord.prsn_id_byr = prsn.prsn_id
GROUP BY prsn.prsn_id
UNION ALL
SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
reward_order_details rdet,
reward_orders rord,
persons prsn
WHERE pnts.pnts_status = 'R'
AND rdet.rdet_id = pnts.rdet_id
AND rord.rord_id = rdet.rord_id
AND rord.prsn_id = prsn.prsn_id
GROUP BY prsn.prsn_id
UNION ALL
SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
miscellaneous_points misp,
persons prsn
WHERE pnts.pnts_status = 'R'
AND pnts.mpts_id = misp.mpts_id
AND misp.prsn_id = prsn.prsn_id
GROUP BY prsn.prsn_id
UNION ALL
SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
transaction_details tdet,
returns rtn,
persons prsn
WHERE pnts.pnts_status = 'R'
AND tdet.tdet_id = pnts.tdet_id
AND tdet.rtn_id = rtn.rtn_id
AND rtn.prsn_id_byr = prsn.prsn_id
GROUP BY prsn.prsn_id)
GROUP BY prsn_id) person_points,
persons prsn
LEFT OUTER JOIN point_summaries psum on psum.prsn_id = prsn.prsn_id
WHERE person_points.points > 0
AND person_points.prsn_id = prsn.prsn_id
AND ((NOT psum.psum_points_available = person_points.points)
OR NOT EXISTS (SELECT 1
FROM point_summaries x
WHERE x.prsn_id = prsn.prsn_id))
返回一个成功的表：
| prsn_id | points | psum_points_available |
|------------------------------------------|
| 111111 | 676 | 287 |
| 111112 | 11672 | 1971 |
| 111113 | 137 | 89 |
| 111114 | 156 | (null) |
| 111115 | 5111 | 570 |
| 111116 | 280 | (null) |
示例 2：
(片段( FROM persons prsn, (SELECT ...) LEFT OUTER JOIN point_summaries psum ON psum.prsn_id prsn.prsn_id
SELECT prsn.prsn_id, person_points.points, psum.psum_points_available
FROM (SELECT prsn_id prsn_id, sum(points) points
FROM persons prsn,
(SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
invoices inv,
transaction_details tdet,
orders ord,
persons prsn
WHERE pnts.pnts_status = 'R'
AND tdet.tdet_id = pnts.tdet_id
AND tdet.inv_id = inv.inv_id
AND inv.ord_id = ord.ord_id
AND ord.prsn_id_byr = prsn.prsn_id
GROUP BY prsn.prsn_id
UNION ALL
SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
reward_order_details rdet,
reward_orders rord,
persons prsn
WHERE pnts.pnts_status = 'R'
AND rdet.rdet_id = pnts.rdet_id
AND rord.rord_id = rdet.rord_id
AND rord.prsn_id = prsn.prsn_id
GROUP BY prsn.prsn_id
UNION ALL
SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
miscellaneous_points misp,
persons prsn
WHERE pnts.pnts_status = 'R'
AND pnts.mpts_id = misp.mpts_id
AND misp.prsn_id = prsn.prsn_id
GROUP BY prsn.prsn_id
UNION ALL
SELECT prsn.prsn_id, SUM(pnts.pnts_points) points
FROM points pnts,
transaction_details tdet,
returns rtn,
persons prsn
WHERE pnts.pnts_status = 'R'
AND tdet.tdet_id = pnts.tdet_id
AND tdet.rtn_id = rtn.rtn_id
AND rtn.prsn_id_byr = prsn.prsn_id
GROUP BY prsn.prsn_id)
GROUP BY prsn_id) person_points
LEFT OUTER JOIN point_summaries psum on psum.prsn_id = prsn.prsn_id
WHERE person_points.points > 0
AND person_points.prsn_id = prsn.prsn_id
AND ((NOT psum.psum_points_available = person_points.points)
OR NOT EXISTS (SELECT 1
FROM point_summaries x
WHERE x.prsn_id = prsn.prsn_id))
返回错误：
ORA-00904: "PRSN"."PRSN_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 49 Column: 16
我一直在寻找答案，但我找到的一切似乎都表明FROM的顺序无关紧要。如果使用SELECT子查询在查询的FROM子句中生成临时表，这是否开始重要？或者也许这里还有其他我错过的东西，有人可以启发我。
提前谢谢。
问题不在于子查询或左连接。问题是您混合了隐式和显式联接语法。
举一个简单得多的例子：
select *
from dual a, dual b join dual c on c.dummy = b.dummy
where b.dummy = a.dummy;
D D D
- - -
X X X
select *
from dual b, dual a join dual c on c.dummy = b.dummy
where b.dummy = a.dummy;
ORA-00904: "B"."DUMMY": invalid identifier
显式JOIN语法优先于FROM中逗号分隔表列表中的隐式连接;因此第一个查询有效地执行：
select *
from dual b
join dual c on c.dummy = b.dummy
join dual a on b.dummy = a.dummy;
D D D
- - -
X X X
而第二个正在有效地做：
select *
from dual a
join dual c on c.dummy = b.dummy
join dual b on b.dummy = a.dummy;
ORA-00904: "B"."DUMMY": invalid identifier
使用该语法，更明显的是，在引用b.dummy的第一点，尚未定义b表别名，因此出现错误。
混合使用这两种联接样式不是一个好主意。我建议你标准化"新的"显式连接语法：
FROM (SELECT ...) person_points
JOIN persons prsn ON prsn.prsn_id = person_points.prsn_id
LEFT OUTER JOIN point_summaries psum ON psum.prsn_id = prsn.prsn_id
或
FROM persons prsn
JOIN (SELECT ...) person_points ON person_points.prsn_id = prsn.prsn_id
LEFT OUTER JOIN point_summaries psum ON psum.prsn_id = prsn.prsn_id