SQLite 支持哪些连接?

2021-12-17 00:00:00 join sqlite

根据join-op 语法，SQLite 有 13 个不同的 join 语句:

According to the join-op syntax, SQLite has 13 distinct join statements:

, JOIN LEFT JOIN OUTER JOIN LEFT OUTER JOIN INNER JOIN CROSS JOIN NATURAL JOIN NATURAL LEFT JOIN NATURAL OUTER JOIN NATURAL LEFT OUTER JOIN NATURAL INNER JOIN NATURAL CROSS JOIN

它们都是独一无二的吗?哪些是等价的?

Are they all unique? Which are equivalent?

推荐答案

SQLite 语法与 SQL-92 规范，据此，以下内容是非法的:

The SQLite grammar is a bit different from the SQL-92 spec's, according to which, the following are illegal:

*OUTER JOIN *NATURAL OUTER JOIN *NATURAL CROSS JOIN

前两个，因为一个，为了包含OUTER，还必须包含一个代码> 在它之前.最后，因为 NATURAL 只能出现在的，而不是的.这些似乎不符合任何规范，因此最好避免使用它们.

The first two, because a <join type>, in order to contain OUTER, must also include an <outer join type> before it. The last, because NATURAL can only occur in <qualified join>'s, not <cross join>'s. These don't appear to behave according to any spec, so it's a good idea to avoid them.

正如在邮件中的回答list，SQLite3只支持三种join:CROSS JOIN、INNER JOIN、LEFT OUTER JOIN.以下是等效的:

As was answered on the mailing list, SQLite3 only supports three joins: CROSS JOIN, INNER JOIN, and LEFT OUTER JOIN. The following are equivalent:

, == CROSS JOIN JOIN == INNER JOIN LEFT JOIN == LEFT OUTER JOIN

如维基百科文章所述，NATURAL 关键字是查找的简写并匹配同名列，不影响连接类型.

As explained in the wikipedia article the NATURAL keyword is shorthand for finding and matching on same-name columns, and doesn't affect the the join type.

根据 SQLite 页面，'RIGHT' 和 '完整' OUTER JOIN 不受支持.

According to the SQLite page, 'RIGHT' and 'FULL' OUTER JOIN's are not supported.

相关文章