隐式内部联接-它们相等吗? [英] implicit inner joins - are they equal?

问题描述

我认为这两个SELECT完全相同(但是我想重写第二个SELECT以帮助我的情况下的优化器)-无论表a，b，c，d中的数据是什么，两个SELECT都会产生完全相同的相同的结果.你同意?谢谢！

  create table a(id number);创建表b(id号)；创建表c(id号)；创建表d(id号)；-第一季度从a，b，c，d中选择*其中a.id = b.id和a.id = c.id和a.id = d.id;-第二季度从a，b，c，d中选择*其中a.id = b.id和a.id = c.id和a.id = d.id-Q2仅在接下来的3行中与Q1不同和b.id = c.id和b.id = d.id和c.id = d.id; 

解决方案

我要解决的问题是，这些不等式是否总是正确.答案是否"，而不是SQL.在大多数情况下，它们是等效的.问题发生在隐式类型转换上.

特别是，如果 a.id 是数字，而其他列是字符串，那么您将遇到以下情况:

  1 ='1'-true1 ='1.00'-true'1'='1.00'-否 

您可以在此db小提琴上看到此.使用 JOIN s进行设置很简单，但是由于我不会在 FROM 子句中编写带有逗号的代码，因此我将把练习留给你.

在实践中，用于联接的id应该是相同的类型.如果没有，您甚至不能声明外键关系.除了最佳实践，这两个查询不会自动等同.

注意:如果您使用正确的，明确的，标准 JOIN 语法，也是如此，我强烈建议您专门学习并使用.

/p>

In my opinion these two SELECTs are exactly equal (but I want to rewrite the first for the second to help the optimizer in my case) - whatever data sits in tables a,b,c,d both SELECTs will produce exactly the same results. Do you agree? Thanks!

create table a (id number);
create table b (id number);
create table c (id number);
create table d (id number);

--Q1
select * from a,b,c,d
where a.id = b.id
and   a.id = c.id 
and   a.id = d.id;

--Q2
select * from a,b,c,d
where a.id = b.id
and   a.id = c.id 
and   a.id = d.id
      -- Q2 differs from Q1 only in the next 3 lines
and   b.id = c.id
and   b.id = d.id
and   c.id = d.id;

解决方案

I am going to address the question of whether those inequalities are always true. The answer is "no", not in SQL. Under most circumstances, they are equivalent. The problem arises with implicit type conversion.

In particular, if a.id is a number and other columns are strings, then you have the situation where:

1 = '1'        -- true
1 = '1.00'     -- true
'1' = '1.00'   -- false

You can see this on this db<>fiddle. It would be trivial to set this up using JOINs, but since I am not going to write code that has commas in the FROM clause, I'll leave that exercise to you.

In practice, id's used for joins should be of the same type. You cannot even declare a foreign key relationship if they are not. That best practice aside, the two queries are not automatically equivalent.

Note: This would be just as true if you used proper, explicit, standard JOIN syntax, which I strongly, strongly encourage you to learn and use exclusively.

这篇关于隐式内部联接-它们相等吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！