草庐IT

mysql - SQL JOIN 查询返回我们在连接表中没有找到匹配项的行

coder 2023-10-03 原文

更多的理论/逻辑问题，但我有两个表:links和 options .链接是一个表，我在其中添加了代表产品 ID(在单独的 products 表中)和选项之间的链接的行。 options表包含所有可用选项。

我想要做的(但努力为其创建逻辑)是连接两个表，仅返回 links 中没有选项链接的行。表，因此表示哪些选项仍可添加到产品中。

是否有 SQL 的功能可以帮助我？我对 SQL 还不是很有经验。

最佳答案

你的 table 设计听起来不错。

如果此查询返回 id链接到特定“产品”的“选项”的值...

SELECT k.option_id
  FROM links k
 WHERE k.product_id = 'foo'

然后此查询将获得与“产品”相关的所有选项的详细信息
SELECT o.id
     , o.name
  FROM options o
  JOIN links k
    ON k.option_id = o.id
 WHERE k.product_id = 'foo'

请注意，我们实际上可以移动 "product_id='foo'"从 WHERE 子句到 JOIN 的 ON 子句的谓词，以获得等效的结果，例如
SELECT o.id
     , o.name
  FROM options o
  JOIN links k
    ON k.option_id = o.id
   AND k.product_id = 'foo'

(并不是说它在这里有什么不同，但如果我们使用 OUTER JOIN 会有所不同(在 WHERE 子句中，它会否定连接的“外部性”，并使其等效于 INNER JOIN。 )

但是，这些都没有回答您的问题，它只是为回答您的问题奠定了基础:

我们如何从未链接到特定产品的“选项”中获取行？

最有效的方法是(通常)反连接图案。

也就是说，我们将从“选项”中获取所有行，以及“链接”中的任何匹配行(对于特定的 product_id，在您的情况下)。该结果集将包括“选项”中在“链接”中没有匹配行的行。

“技巧”是过滤掉在“链接”中找到匹配行的所有行。这将留给我们 只有没有匹配的行。

我们过滤这些行的方式是在 WHERE 子句中使用一个谓词来检查是否找到了匹配项。我们通过检查我们确定将是 的列来做到这一点。非空 如果找到匹配的行。我们肯定知道*该列将是 空 如果匹配的行是 不是 成立。

像这样的东西:
SELECT o.id
     , o.name
  FROM options o
  LEFT
  JOIN links k
    ON k.option_id = o.id
   AND k.product_id = 'foo'
 WHERE k.option_id IS NULL
"LEFT"关键字指定“外部”连接操作，即使没有找到匹配的行，我们也会从“选项”(连接“左侧”的表)中获取所有行。 (普通的内连接会过滤掉没有匹配的行。)

“技巧”在 WHERE 子句中……如果我们从链接中找到匹配的行，我们就知道 "option_id"从 "links" 返回的列不会为NULL。如果它“等于”某些东西，它就不能为 NULL，而且我们知道它必须“等于”某些东西，因为 ON 子句中的谓词。

因此，我们知道没有匹配项的选项行将具有该列的 NULL 值。

让你的大脑围绕它需要一点时间，但反连接很快就变成了一种熟悉的模式。

“反连接”模式不是获得结果集的唯一方法。还有其他几种方法。

一种选择是使用带有 "NOT EXISTS" 的查询带有相关子查询的谓词。这更容易理解，但通常效果不佳:
SELECT o.id
     , o.name
  FROM options o
 WHERE NOT EXISTS ( SELECT 1
                      FROM links k
                     WHERE k.option_id = o.id
                       AND k.product_id = 'foo'
                  )

这表示从选项表中获取所有行。但是对于每一行，运行一个查询，并查看链接表中是否“存在”匹配的行。 (选择列表中返回什么并不重要，我们只是测试它是否至少返回一行......我在选择列表中使用“1”来提醒我我正在寻找“1行” ”。

这通常不如反连接执行得好，但有时它确实运行得更快，特别是如果外部查询的 WHERE 子句中的其他谓词几乎过滤掉了每一行，并且子查询只需要运行几个行。 (也就是说，当我们只需要检查干草堆中的几根针时。当我们需要处理整个干草堆时，反连接模式通常更快。)

您最有可能看到的初学者查询是 NOT IN (subquery) .我什至不打算举一个例子。如果你有一个文字列表，那么一定要使用 NOT IN。但是对于子查询，它很少是表现最好的，尽管它似乎是最容易理解的。

哦，干草，我也会给出一个演示(不是我鼓励你这样做):
SELECT o.id
     , o.name
  FROM options o
 WHERE o.id NOT IN ( SELECT k.option_id
                       FROM links k
                      WHERE k.product_id = 'foo'
                        AND k.option_id IS NOT NULL
                      GROUP BY k.option_id
                   )

该子查询(在括号内)获取与产品关联的所有 option_id 值的列表。

现在，对于 options 中的每一行(在外部查询中)，我们可以检查 id 值以查看它是否在子查询返回的列表中。

如果我们保证 option_id 永远不会为 NULL，我们可以省略测试 "option_id IS NOT NULL" 的谓词。 . (在更一般的情况下，当 NULL 进入结果集时，外部查询无法判断 o.id 是否在列表中，并且查询不返回任何行；所以我通常包括，即使不是必需的。GROUP BY 也不是绝对必要的；特别是如果 (product_id,option_id) 元组上有唯一约束(保证唯一性)。

但是，同样，不要使用 NOT IN (subquery) ，除了测试，除非有一些令人信服的理由(例如，它设法比反连接执行得更好。)

您不太可能注意到小集合的任何性能差异，传输语句、解析它、生成访问计划和返回结果的开销使计划的实际“执行”时间相形见绌。 “执行”时间的差异在更大的集合中变得明显。
EXPLAIN SELECT ...是处理执行计划的一种非常好的方法，可以查看 MySQL 对您的语句真正执行的操作。

适当的索引，尤其是覆盖索引，可以显着提高某些语句的性能。

关于mysql - SQL JOIN 查询返回我们在连接表中没有找到匹配项的行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/22975556/

mysqlJOINcodebroption_idsql

有关mysql - SQL JOIN 查询返回我们在连接表中没有找到匹配项的行的更多相关文章

  1. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  2. ruby - 难道Lua没有和Ruby的method_missing相媲美的东西吗？ - 2

    我好像记得Lua有类似Ruby的method_missing的东西。还是我记错了？ 最佳答案 表的metatable的__index和__newindex可以用于与Ruby的method_missing相同的效果。 关于ruby-难道Lua没有和Ruby的method_missing相媲美的东西吗？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/7732154/

  3. ruby - 为什么 4.1%2 使用 Ruby 返回 0.0999999999999996？但是 4.2%2==0.2 - 2

    为什么4.1%2返回0.0999999999999996？但是4.2%2==0.2。 最佳答案 参见此处:WhatEveryProgrammerShouldKnowAboutFloating-PointArithmetic实数是无限的。计算机使用的位数有限(今天是32位、64位)。因此计算机进行的浮点运算不能代表所有的实数。0.1是这些数字之一。请注意，这不是与Ruby相关的问题，而是与所有编程语言相关的问题，因为它来自计算机表示实数的方式。 关于ruby-为什么4.1%2使用Ruby返

  4. ruby-on-rails - rails 目前在重启后没有安装 - 2

    我有一个奇怪的问题:我在rvm上安装了ruby​​onrails。一切正常，我可以创建项目。但是在我输入“railsnew”时重新启动后，我有“程序'rails'当前未安装。”。SystemUbuntu12.04ruby-v"1.9.3p194"gemlistactionmailer(3.2.5)actionpack(3.2.5)activemodel(3.2.5)activerecord(3.2.5)activeresource(3.2.5)activesupport(3.2.5)arel(3.0.2)builder(3.0.0)bundler(1.1.4)coffee-rails(

  5. ruby 正则表达式 - 如何替换字符串中匹配项的第 n 个实例 - 2

    在我的应用程序中，我需要能够找到所有数字子字符串，然后扫描每个子字符串，找到第一个匹配范围(例如5到15之间)的子字符串，并将该实例替换为另一个字符串“X”。我的测试字符串s="1foo100bar10gee1"我的初始模式是1个或多个数字的任何字符串，例如，re=Regexp.new(/\d+/)matches=s.scan(re)给出["1","100","10","1"]如果我想用“X”替换第N个匹配项，并且只替换第N个匹配项，我该怎么做？例如，如果我想替换第三个匹配项“10”(匹配项[2])，我不能只说s[matches[2]]="X"因为它做了两次替换“1fooX0barXg

  6. ruby - 匹配未转义的平衡定界符对 - 2

    如何匹配未被反斜杠转义的平衡定界符对(其本身未被反斜杠转义)(无需考虑嵌套)？例如对于反引号，我试过了，但是转义的反引号没有像转义那样工作。regex=/(?!$1:"how\\"#expected"how\\`are"上面的正则表达式不考虑由反斜杠转义并位于反引号前面的反斜杠，但我愿意考虑。StackOverflow如何做到这一点？这样做的目的并不复杂。我有文档文本，其中包括内联代码的反引号，就像StackOverflow一样，我想在HTML文件中显示它，内联代码用一些spanMaterial装饰。不会有嵌套，但转义反引号或转义反斜杠可能出现在任何地方。

  7. ruby - 在没有 sass 引擎的情况下使用 sass 颜色函数 - 2

    我想在一个没有Sass引擎的类中使用Sass颜色函数。我已经在项目中使用了sassgem，所以我认为搭载会像以下一样简单:classRectangleincludeSass::Script::FunctionsdefcolorSass::Script::Color.new([0x82,0x39,0x06])enddefrender#hamlengineexecutedwithcontextofself#sothatwithintemlateicouldcall#%stop{offset:'0%',stop:{color:lighten(color)}}endend更新:参见上面的#re

  8. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  9. ruby - 检查字符串是否包含散列中的任何键并返回它包含的键的值 - 2

    我有一个包含多个键的散列和一个字符串，该字符串不包含散列中的任何键或包含一个键。h={"k1"=>"v1","k2"=>"v2","k3"=>"v3"}s="thisisanexamplestringthatmightoccurwithakeysomewhereinthestringk1(withspecialcharacterslike(^&*$#@!^&&*))"检查s是否包含h中的任何键的最佳方法是什么，如果包含，则返回它包含的键的值？例如，对于上面的h和s的例子，输出应该是v1。编辑:只有字符串是用户定义的。哈希将始终相同。 最佳答案

  10. ruby - 匹配大写字母并用后续字母填充，直到一定的字符串长度 - 2

    我有一个驼峰式字符串，例如:JustAString。我想按照以下规则形成长度为4的字符串:抓取所有大写字母；如果超过4个大写字母，只保留前4个；如果少于4个大写字母，则将最后大写字母后的字母大写并添加字母，直到长度变为4。以下是可能发生的3种情况:ThisIsMyString将产生TIMS(大写字母)；ThisIsOneVeryLongString将产生TIOV(前4个大写字母)；MyString将生成MSTR(大写字母+tr大写)。我设法用这个片段解决了前两种情况:str.scan(/[A-Z]/).first(4).join但是，我不太确定如何最好地修改上面的代码片段以处理最后一种

随机推荐