MySQL全内连接

发布于 2024-11-06 02:56:06 字数 435 浏览 3 评论 0原文

我

SELECT clientReport.id 
FROM clientReport 
LEFT JOIN report02 ON (report02.id = clientReport.id)
WHERE report02.id is null;

这样做相当于

SELECT clientReport.id
WHERE clientReport.rowNumber NOT EXISTS (
SELECT clientReport.rowNumber FROM report02, clientReport
WHERE report02.id=clientReport.id);

我需要一个完整的内部联接，以便在 report02 中也获得不匹配，而不仅仅是 clientReport。我该如何编写连接来做到这一点？

原文

I have

SELECT clientReport.id 
FROM clientReport 
LEFT JOIN report02 ON (report02.id = clientReport.id)
WHERE report02.id is null;

that does the equivalent of

SELECT clientReport.id
WHERE clientReport.rowNumber NOT EXISTS (
SELECT clientReport.rowNumber FROM report02, clientReport
WHERE report02.id=clientReport.id);

I need, presumably, a full inner join to also get mismatches in report02, not just clientReport. How do I write the join to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦初启 2024-11-13 02:56:06

下面的应该可以工作。

SELECT clientReport.id,report02.id
FROM clientReport 
FULL OUTER JOIN report02 ON (report02.id = clientReport.id)
WHERE report02.id is null
OR clientReport.id is null;

它应该，但事实并非如此（因为 MySQL 目前不支持 FULL OUTER JOIN。）

这更有可能起作用：

( SELECT clientReport.id  AS report01
       , report02.id      AS report02
  FROM clientReport 
  LEFT  OUTER JOIN report02
      ON (report02.id = clientReport.id)
  WHERE report02.id IS NULL
)
UNION
( SELECT clientReport.id  AS report01
       , report02.id      AS report02
  FROM clientReport 
  RIGHT OUTER JOIN report02
      ON (report02.id = clientReport.id)
  WHERE clientReport.id is null
)

The below should work.

SELECT clientReport.id,report02.id
FROM clientReport 
FULL OUTER JOIN report02 ON (report02.id = clientReport.id)
WHERE report02.id is null
OR clientReport.id is null;

It should but it doesn't (as MySQL does not currently support FULL OUTER JOIN.)

This is more likely to work:

( SELECT clientReport.id  AS report01
       , report02.id      AS report02
  FROM clientReport 
  LEFT  OUTER JOIN report02
      ON (report02.id = clientReport.id)
  WHERE report02.id IS NULL
)
UNION
( SELECT clientReport.id  AS report01
       , report02.id      AS report02
  FROM clientReport 
  RIGHT OUTER JOIN report02
      ON (report02.id = clientReport.id)
  WHERE clientReport.id is null
)

回复收藏 0 原文

~没有更多了~