SQL中的where子句条件 [英] where clause conditions in SQL

问题描述

下面是一个基于 where 子句的连接:

Below is a join based on where clause:

SELECT a.* FROM TEST_TABLE1 a,TEST_TABLE2 b,TEST_TABLE3 c
WHERE a.COL11 = b.COL11
AND b.COL12 = c.COL12
AND a.COL3 = c.COL13;

我一直在从在线资源中学习 SQL 并尝试用连接转换它

I have been learning SQL from online resources and trying to convert it with joins

推荐答案

两个问题:

• 原始查询令人困惑.外连接(带有 (+) 后缀)与最后一个 where 条件无关.由于这种情况，查询应该只返回实际匹配 c 记录的记录.所以原始查询与没有这样的 (+) 后缀一样.
• 您的查询连接了 TEST_TABLE3 两次，而第一个查询只连接了一次，并且有两个条件决定了它在那里的连接方式.您不应将这些条件拆分为两个单独的连接.

• The original query is confusing. The outer joins (with the (+) suffix) are made irrelevant by the last where condition. Because of that condition, the query should only return records where there is an actual matching c record. So the original query is the same as if there were no such (+) suffixes.
• Your query joins TEST_TABLE3 twice, while the first query only joins it once, and there are two conditions that determine how it is joined there. You should not split those conditions over two separate joins.

顺便说一句，令人惊讶的是 SQL Fiddle 站点没有显示错误，因为两次使用相同的别名是没有意义的.例如，请参阅 MySQL 如何在 dbfiddle (在所有可用的 MySQL 版本上):

BTW, it is surprising that the SQL Fiddle site does not show an error, as it makes no sense to use the same alias twice. See for example how MySQL returns the error with the same query on dbfiddle (on all available versions of MySQL):

不是唯一的表/别名:'C'

Not unique table/alias: 'C'

所以要使用标准的 join 表示法获得相同的结果，所有连接都应该是 inner 连接:

So to get the same result using the standard join notation, all joins should be inner joins:

SELECT * 
FROM  TEST_TABLE1 A
INNER JOIN  TEST_TABLE2 B
        ON A.COL11 = B.COL11
INNER JOIN TEST_TABLE3 C
        ON A.COL11 = B.COL11
        AND B.COL12 = C.COL12;

这篇关于SQL中的where子句条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！