当前位置：文江博客话题详情

SQL - 使用 UNION 进行连接 ??使用 JOIN 进行联合？

发布于 2024-11-18 09:41:18 字数 227 浏览 2 评论 0原文

我在一次采访中被问到这个问题。可以使用 UNION 关键字进行 JOIN 吗？可以使用 JOIN 关键字进行 UNION 吗？

那是 - 1. 我应该在不使用 JOIN 关键字但使用 UNION 关键字的情况下获得与 JOIN 相同的输出？ 2. 我应该在不使用 UNION 关键字但使用 JOIN 关键字的情况下获得与 UNION 相同的输出？

如果可能的话，你能给我一个如何做到这一点的例子吗？

原文

分享到QQ

分享到微博

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

发布评论

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

深府石板幽径 2024-11-25 09:41:18

面试是你展示自己作品的框架。记住：不要回答问题；）

想想新闻发布会：发言人并不打算回答记者提出的难题来让自己陷入困境。相反，他们正在寻找他们已经有了答案的问题，即他们想要发布的信息（仅此而已！）

如果我在面试中遇到这个问题，我会用它来展示我的关系代数知识，因为这就是我本来是带着这样做的意图去接受采访的；我对“在这里谈论关系代数”问题保持警惕，就是这样。

宽松地说，JOIN 相当于逻辑 AND，而 UNION 相当于逻辑 OR。因此，使用约定逻辑的类似问题可能是“你能使用 OR 进行 AND 操作吗？”和“你能用 AND 做 OR 吗？”答案将取决于您还可以使用什么，例如 NOT 可能会派上用场；）

我也很想讨论原始运算符集、计算完整性所需的运算符集以及运算符和速记集之间的差异出于实际目的所需。

试图直接回答这个问题会引发进一步的问题。 JOIN 在关系代数中意味着“自然连接”，而在 SQL 中它意味着INNER JOIN。如果问题具体与 SQL 相关，您是否必须回答所有 JOIN 类型？ UNION JOIN 怎么样？

举一个例子，SQL 的外连接是著名的UNION。克里斯·戴特 (Chris Date) 的表达比我想象的要好：

外部连接的明确设计目的是
在其结果中产生空值并且应该
因此一般情况下应避免。
从关系上来说，这是一种
猎枪婚姻：它迫使桌子
进入某种联盟——是的，我的意思是
联合，而不是连接——即使表
有问题的不符合
工会的通常要求（参见
第 6 章）。它实际上是这样做的，
通过填充一个或两个表
在进行并集之前使用空值，
从而使它们符合那些
毕竟通常的要求。但
没有理由这样填充
不应该用正确的值来完成
而不是空值

SQL 和关系理论，第一版，作者：CJ Date

如果您想在采访中表达“我讨厌空值”，这将是一个很好的讨论点！

这些只是浮现在脑海中的一些想法。关键的一点是，通过问这些问题，面试官向你提供了一个分支。你会挂什么？ ;)

An interview is the framework on which you set out your wares. Remember: don't answer questions ;)

Think of a press conference: the spokesperson is not looking to answer difficult questions from journos to catch themselves out. Rather, they are looking for questions to which they already have answers, being the information they want to release (and no more!)

If I faced this question in an interview, I would use it to demonstrate my knowledge of relational algebra because that's what I'd have gone into the interview with the intention of doing; I be alert for the "Talk about relational algebra here" question and this would be it.

Loosely speaking, JOIN is the counterpart of logical AND, whereas UNION is the counterpart of logical OR. Therefore, similar questions using convention logic could be, "Can you do AND using OR?" and "Can you do OR using AND?" The answer would depend on what else you could use e.g. NOT might come in handy ;)

I'd also be tempted to discuss the differences between the set of primitive operators, the set of operators necessary for computational completeness and the set of operators and shorthands required for practical purposes.

Trying to answer the question directly raises further questions. JOIN implies 'natural join' in relational algebra whereas in SQL it implies INNER JOIN. If the question specifically relates to SQL, do you have to answer for all the JOIN types? What about UNION JOIN?

To employ one example, SQL's outer join is famously a UNION. Chris Date expresses it better than I could ever hope to:

Outer join is expressly designed to
produce nulls in its result and should
therefore be avoided, in general.
Relationally speaking, it's a kind of
shotgun marriage: It forces tables
into a kind of union—yes, I do mean
union, not join—even when the tables
in question fail to conform to the
usual requirements for union (see
Chapter 6). It does this, in effect,
by padding one or both of the tables
with nulls before doing the union,
thereby making them conform to those
usual requirements after all. But
there's no reason why that padding
shouldn't be done with proper values
instead of nulls

SQL and Relational Theory, 1st Edition by C.J. Date

This would be a good discussion point if, "I hate nulls" is something you wanted to get across in the interview!

These are just a few thoughts that spring to mind. The crucial point is, by asking these questions the interviewer is offering you a branch. What will YOU hang on it? ;)

回复收藏 0 原文

怪异←思 2024-11-25 09:41:18

由于这是一个面试问题，他们正在测试您对这两个功能的理解。

他们期望的可能答案是“通常不，你不能这样做，因为他们执行不同的操作”，并且你可以通过声明联合将行附加到结果集的末尾，其中作为联接添加更多列来更详细地解释这一点。

进行 Join 和 Union 工作的唯一方法是，行仅包含来自两个源之一的数据：

SELECT A.AA, '' AS BB FROM A
UNION ALL 
SELECT '' AS AA, B.BB FROM B

与以下内容相同：

SELECT ISNULL(A.AA, '') AS AA, ISNULL(B.BB, '') AS BB FROM A
FULL OUTER JOIN B ON 1=0

或者仅使用类型匹配的一列执行此操作：与以下情况

SELECT A.AA AS TT FROM A
UNION ALL 
SELECT B.BB AS TT FROM B

相同：

SELECT ISNULL(A.AA, B.AA) AS TT
FROM A
FULL OUTER JOIN B ON 1=0

一种情况如果您在多个表上生成了数据，但您希望同时查看所有数据，那么您会这样做，但是我建议在这种情况下使用 UNION 而不是 FULL OUTER JOIN，因为查询正在执行您本来会执行的操作预计。

As this is an interview question, they are testing your understanding of both these functions.

The likely answer they are expecting is "generally no you cannot do this as they perform different actions", and you would explain this in more detail by stating that a union appends rows to the end of the result set where as a join adds further columns.

The only way you could have a Join and a Union work is where rows contain data from only one of the two sources:

SELECT A.AA, '' AS BB FROM A
UNION ALL 
SELECT '' AS AA, B.BB FROM B

Is the same as:

SELECT ISNULL(A.AA, '') AS AA, ISNULL(B.BB, '') AS BB FROM A
FULL OUTER JOIN B ON 1=0

Or to do this with only one column where the types match:

SELECT A.AA AS TT FROM A
UNION ALL 
SELECT B.BB AS TT FROM B

Is the same as:

SELECT ISNULL(A.AA, B.AA) AS TT
FROM A
FULL OUTER JOIN B ON 1=0

One case where you would do this is if you have data spawned over multiple tables but you want to see ti all together, however I would advise to use a UNION in this case rather than a FULL OUTER JOIN because of the query is doing what you would otherwise expect.

回复收藏 0 原文

唔猫 2024-11-25 09:41:18

你的意思是这样的吗？

create table Test1 (TextField nvarchar(50), NumField int)
create table Test2 (NumField int)
create table Test3 (TextField nvarchar(50), NumField int)

insert into Test1 values ('test1a', 1)
insert into Test1 values ('test1b', 2)
insert into Test2 values (1)
insert into Test3 values ('test3a', 4)
insert into Test3 values ('test3b', 5)

select Test1.*
from Test1 inner join Test2 on Test1.NumField = Test2.NumField
union
select * from Test3

（在 SQL Server 2008 上编写）

当两个 SELECT 语句具有相同的列数并且这些列具有相同的列数（或在最不相似的）数据类型。
UNION 不关心两个 SELECT 语句是否仅从单个表中选择数据，或者其中一个或两个已经是 JOIN不止一张桌子。

Do you mean something like this?

create table Test1 (TextField nvarchar(50), NumField int)
create table Test2 (NumField int)
create table Test3 (TextField nvarchar(50), NumField int)

insert into Test1 values ('test1a', 1)
insert into Test1 values ('test1b', 2)
insert into Test2 values (1)
insert into Test3 values ('test3a', 4)
insert into Test3 values ('test3b', 5)

select Test1.*
from Test1 inner join Test2 on Test1.NumField = Test2.NumField
union
select * from Test3

(written on SQL Server 2008)

UNION works when both SELECT statements have the same number of columns, AND the columns have the same (or at least similar) data types.
UNION doesn't care if both SELECT statements select data only from a single table, or if one or both of them are already JOINs on more than one table.

回复收藏 0 原文

滿滿的愛 2024-11-25 09:41:18

我认为这还取决于其他可用的操作。

如果我没记错的话，UNION 可以使用 FULL OUTER 连接来完成：

Table a (x, y)

Table b (x, y) 

CREATE VIEW one
AS
SELECT a.x AS Lx
     , b.x AS Rx
     , a.y AS Ly
     , b.y AS Ry
FROM a FULL OUTER JOIN b
         ON  a.x = b.x
         AND a.y = b.y


CREATE VIEW unionTheHardWay
AS
SELECT COALESCE(Lx, Rx) AS x
     , COALESCE(Ly, Ry) AS y
FROM one

I think it also depends on other operations available.

If I remember well, UNION can be done using a FULL OUTER join:

Table a (x, y)

Table b (x, y) 

CREATE VIEW one
AS
SELECT a.x AS Lx
     , b.x AS Rx
     , a.y AS Ly
     , b.y AS Ry
FROM a FULL OUTER JOIN b
         ON  a.x = b.x
         AND a.y = b.y


CREATE VIEW unionTheHardWay
AS
SELECT COALESCE(Lx, Rx) AS x
     , COALESCE(Ly, Ry) AS y
FROM one

回复收藏 0 原文

~没有更多了~