I was asked this question during one of my interviews. Can you do JOIN using UNION keyword? Can you do UNION using JOIN keyword?
我在一次采访中被问到这个问题。你能用UNION关键字连接吗?可以使用JOIN关键字进行联合吗?
That is - 1. I should get same output as JOIN without using JOIN keyword, but using UNION Keyword? 2. I should get same output as UNION without using UNION keyword, but using JOIN Keyword?
这是- 1。我应该得到与JOIN相同的输出，而不是使用JOIN关键字，而是使用UNION关键字?2。我应该不使用UNION关键字而使用JOIN关键字来获得与UNION相同的输出吗?
Can you give me an example of how to do this if possible?
如果可能的话，你能给我举个例子吗?
4 个解决方案
#1
9
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 ;)
松散地说，连接是逻辑的对等物，而UNION是逻辑或的对等物。因此，使用约定逻辑的类似问题可以是，“您能做和使用OR吗?”和“您能做或使用AND吗?”答案将取决于你还能使用什么例如: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.
我还想讨论一组原始运算符之间的区别，计算完整性所必需的操作符集合，以及实用目的所需的操作符和shorthands集合。
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?
试图直接回答这个问题会引出更多的问题。连接在关系代数中意味着“自然连接”，而在SQL中则意味着内部连接。如果这个问题与SQL有关，那么您是否需要对所有的连接类型负责?联盟加入呢?
To employ one example, SQL's outer join is famously a UNION. Chris Date expresses it better than I could ever hope to:
举个例子，众所周知，SQL的外部连接是一个联合。Chris Date表达得比我希望的更好:
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
外部连接被明确地设计为在其结果中产生空值，因此一般应该避免。关系来说,这是一种联姻:它迫使表成一种union-yes,我的意思是,不是join-even当表的问题未能符合常规要求联盟(见第6章)。它,实际上,通过填充一个或两个表null之前做结合,从而使他们符合这些通常的要求。但是没有理由说填充不应该用合适的值而不是null来完成
SQL and Relational Theory, 1st Edition by C.J. Date
SQL和关系理论，第一版，C.J. Date
This would be a good discussion point if, "I hate nulls" is something you wanted to get across in the interview!
这将是一个很好的讨论点，如果，“我讨厌nulls”是你在面试中想要表达的东西!
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? ;)
这些只是我脑海中闪现的一些想法。关键的一点是，通过问这些问题，面试官会给你提供一个分支。你要挂在上面什么?,)
#2
9
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.
他们期望的可能答案是“一般来说，不，您不能这样做，因为它们执行不同的操作”，您可以通过声明一个union将行附加到结果集的末尾来更详细地解释这一点，因为在那里，一个join将添加更多的列。
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.
一个情况下你会做这是如果你有数据催生了多个表,但你想看到ti一起,但是我建议使用工会在这种情况下,而不是一个完整外部联接的查询是做你原本期望。
#3
1
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)
(写于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.
当两个SELECT语句具有相同的列数，并且列具有相同(或至少相似)的数据类型时，UNION就会工作。UNION并不关心这两个SELECT语句只从单个表中选择数据，或者它们中的一个或两个已经连接到多个表上。
#4
1
I think it also depends on other operations available.
我认为这也取决于其他可用的操作。
If I remember well, UNION can be done using a FULL OUTER join:
如果我记得很清楚，可以使用完整的外部连接完成UNION:
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
#1
9
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 ;)
松散地说，连接是逻辑的对等物，而UNION是逻辑或的对等物。因此，使用约定逻辑的类似问题可以是，“您能做和使用OR吗?”和“您能做或使用AND吗?”答案将取决于你还能使用什么例如: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.
我还想讨论一组原始运算符之间的区别，计算完整性所必需的操作符集合，以及实用目的所需的操作符和shorthands集合。
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?
试图直接回答这个问题会引出更多的问题。连接在关系代数中意味着“自然连接”，而在SQL中则意味着内部连接。如果这个问题与SQL有关，那么您是否需要对所有的连接类型负责?联盟加入呢?
To employ one example, SQL's outer join is famously a UNION. Chris Date expresses it better than I could ever hope to:
举个例子，众所周知，SQL的外部连接是一个联合。Chris Date表达得比我希望的更好:
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
外部连接被明确地设计为在其结果中产生空值，因此一般应该避免。关系来说,这是一种联姻:它迫使表成一种union-yes,我的意思是,不是join-even当表的问题未能符合常规要求联盟(见第6章)。它,实际上,通过填充一个或两个表null之前做结合,从而使他们符合这些通常的要求。但是没有理由说填充不应该用合适的值而不是null来完成
SQL and Relational Theory, 1st Edition by C.J. Date
SQL和关系理论，第一版，C.J. Date
This would be a good discussion point if, "I hate nulls" is something you wanted to get across in the interview!
这将是一个很好的讨论点，如果，“我讨厌nulls”是你在面试中想要表达的东西!
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? ;)
这些只是我脑海中闪现的一些想法。关键的一点是，通过问这些问题，面试官会给你提供一个分支。你要挂在上面什么?,)
#2
9
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.
他们期望的可能答案是“一般来说，不，您不能这样做，因为它们执行不同的操作”，您可以通过声明一个union将行附加到结果集的末尾来更详细地解释这一点，因为在那里，一个join将添加更多的列。
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.
一个情况下你会做这是如果你有数据催生了多个表,但你想看到ti一起,但是我建议使用工会在这种情况下,而不是一个完整外部联接的查询是做你原本期望。
#3
1
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)
(写于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.
当两个SELECT语句具有相同的列数，并且列具有相同(或至少相似)的数据类型时，UNION就会工作。UNION并不关心这两个SELECT语句只从单个表中选择数据，或者它们中的一个或两个已经连接到多个表上。
#4
1
I think it also depends on other operations available.
我认为这也取决于其他可用的操作。
If I remember well, UNION can be done using a FULL OUTER join:
如果我记得很清楚，可以使用完整的外部连接完成UNION:
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