从一个表中选择all，其中有些列匹配另一个Select

I have two differents tables. The have some columns in common, for this example lets say 'name' and 'id'.

我有两个不同的表格。它们有一些相同的列，在本例中，我们输入'name'和'id'。

By making

通过使

(   SELECT name,id FROM table1
    EXCEPT
    SELECT name,id FROM table2)  
UNION ALL
(   SELECT name,id FROM table2
    EXCEPT
    SELECT name,id FROM table1)

I get a list of the elements that are on one tablet but not in the other one.

我得到了一个元素列表，这些元素在一个平板电脑上，但在另一个平板电脑上没有。

Up to this point everything is OK.

到目前为止一切正常。

But now, I want to make a select all from table1 where the name and the id matches the result of the query above.

但是现在，我想在表1中创建一个select all，其中名称和id与上面查询的结果匹配。

3 个解决方案

#1

After lots of comments I think this is what you're after...

经过很多评论，我认为这就是你想要的……

SELECT T1.* 
FROM table1 t1
LEFT JOIN table2 t2
   on T1.ID = T2.ID
  and T1.Name = T2.Name
  AND E2.event_Time_UTC between  convert(datetime,'2016-02-09 00:00:20',101) and convert(datetime '2016-02-09 23:59:52',101)
WHERE T2.Name is null
 AND E1.Event_Time_UTC between  convert(datetime,'2016-02-09 00:00:20',101) and convert(datetime,'2016-02-09 23:59:52',101)

you may allow implicit casting to work but above is the explicit approach...

您可以允许隐式转换工作，但以上是显式方法……

if not then you would need to cast the string dates to a date time, assuming Event_Time_UTC is a date/time datatype.

如果不是，则需要将字符串日期转换为日期时间，假设Event_Time_UTC是日期/时间数据类型。

a left join lets us return all records from the 1st table and only those that match from the 2nd.

左连接允许我们返回来自第一个表的所有记录，并且只返回与第二个表匹配的记录。

the t1.* returns only the columns from table1. The join critiera (on) allows us to identify those records which match so they can then be eliminated in the where cluase by 'where t2.name is null' they will always be null when no record match in t2.

t1。*只返回表1中的列。join critiera (on)允许我们识别那些匹配的记录，这样它们就可以在“where t2名称为null”的情况下被删除，当t2中没有记录匹配时，它们将始终为null。

Thus you get a result set that is: all records from t1 without a matching record on name and id in table2.

这样就得到了一个结果集:所有来自t1的记录，而没有在表2中的名称和id的匹配记录。

----------CONTENT BELOW NO LONGER RELEVANT BASED ON COMMENTS------

---------下面的内容不再与评论相关

I Redacted previous answer alot because using sql server not mySQL and I know you want multiple records not table1 and table2 joined.

我修改了之前的答案，因为使用sql server不是mySQL，我知道你想要多个记录，而不是table1和table2。

In the below I create two tables: table1 and table2. I then populate table1 and table2 with some sample data

在下面我创建了两个表:表1和表2。然后我用一些示例数据填充表1和表2

I then show how to get only those records which exist in one table but not the other; returning a separate ROW for each. I then go into detail as to why I choose this approach vs others. I'll finally review what you've tried and try to explain why I don't think it will work...

然后，我将展示如何只获取存在于一个表中的记录，而不获取另一个表中的记录;分别返回一行。然后我详细地解释为什么我选择这种方法而不是其他方法。我最后将回顾你的尝试，并试图解释为什么我认为它行不通……

create table table1 (
  ID int,
  name varchar(20),
  col1 varchar(20),
  col2 varchar(20),
  col3 varchar(20));

Create table table2 (
  id int,
  name varchar(20));

Insert into table1 values (1,'John','col1','col2','col3');
Insert into table1 values (2,'Paul','col1','col2','col3');
Insert into table1 values (3,'George','col1','col2','col3');

Insert into table2 values (1,'John');
Insert into table2 values (4,'Ringo');

Option 1

选项1

SELECT T1.name, T1.ID, T1.Col1, T1.Col2, T1.Col3
FROM Table1 T1
LEFT JOIN Table2 T2
 on T1.Name = T2.Name
and T1.ID = T2.ID
WHERE T2.ID is null  

UNION ALL

SELECT T2.name, T2.ID, NULL, NULL, NULL
FROM Table1 T1
RIGHT JOIN Table2 T2   
 on T1.Name = T2.Name
and T1.ID = T2.ID
WHERE T1.ID is null  ;

which results in...

导致……

Notice John isn't there as it's in both tables. We have the other 2 records from table1, and the ID, name from table2 you're after.

注意约翰不在那里，因为它在两个表格中。我们还有表1的另外两条记录，还有ID，你要的表2的名字。

Normally I would do this as a full outer join but since I think you want to reuse the name and id fields to relate to BOTH tables in the same column we had to use this approach and spell out all the column names in table 1 and put NULL for each column in table1 when displaying records from table2 in order to make the output of the second query union to the first. We simply can't use *

通常我会做这是一个完全外连接但是因为我想你想要重用的名称和id字段与两个表在同一列我们不得不使用这种方法,拼出所有的列名在表1中,把空表的每一列在表1显示记录为了让第二个查询的输出联盟第一。我们就是不能用*

Option 2: Using a full outer join... with all columns from T1

选项2:使用完整的外部连接…所有的列都来自T1

SELECT T1.*
FROM Table1 T1
FULL OUTER JOIN Table2 T2
 on T1.ID = T2.ID
 and T1.Name = T2.Name
WHERE (T1.ID is null or T2.ID is null)

you get this... which doesn't show Ringo...

你得到这个……不显示林格……

But Then i would ask why you need anything from Table 2.... at all so I think you're wanting to still show the ID, Name from table2 when it doesn't exist in table1.

然后我会问为什么你需要任何东西,从表2 ....我认为你仍然需要显示ID，来自表2的名字当它在表1中不存在的时候。

Which is why What I think you're after is the results from the 1st query using the union all..

这就是为什么我认为你在追求的是第一次查询的结果使用了union all..

Option 3 I suppose we could avoid the second query in option 1 by doing...

选项3我认为我们可以通过以下步骤来避免选项1中的第二个查询。

SELECT coalesce(T1.Name, T2.name) as name, coalesce(T1.Id,T2.ID) as ID, T1.col1, T1.Col2, T1.Col3
FROM Table1 T1
FULL OUTER JOIN Table2 T2
 on T1.ID = T2.ID
 and T1.Name = T2.Name
WHERE (T1.ID is null or T2.ID is null)

which gives us... as what I believe to be the desired results as well.

这给了我们……正如我所相信的那样，这也是我所期望的结果。

This works because we know we only want the name,id from table2 and all the column values in table1 will be blank.

这是可行的，因为我们知道我们只想要表2中的名称id，表1中的所有列值都是空的。

Notice however in all cases we simply can't use Tablename.* to select all records from table1.

注意，在所有情况下，我们都不能使用Tablename。*选择表1中的所有记录。

This is what you tried:

这就是你所尝试的:

(   SELECT name,id FROM table1
    EXCEPT
    SELECT name,id FROM table2)  
UNION ALL
(   SELECT name,id FROM table2
    EXCEPT
    SELECT name,id FROM table1)

Assuming you want to reuse the ID, Name fields; you can't select *. Why? because the records in Table2 not in table1 aren't in table1. In my example if you want Ringo to show up you have to reference table2! Additionally, * gives you no ability to "Coalesce" the ID and name fields together as I did in option 3 above.
假设您希望重用ID、名称字段;你不能选择*。为什么?因为表2中不是表1中的记录不在表1中。在我的示例中，如果您希望Ringo出现，则必须引用表2!此外，*无法将ID和名称字段“合并”到上面的选项3中。
If you ONLY want the columns from table1, that means you will NEVER see data from table2... If you don't need the data from table2, (such as ringo in my example) then why do we need to do the union at all?) I'm assuming you want ringo, thus you HAVE to somewhere reference name, id from table2.
如果您只想要表1中的列，这意味着您永远不会看到表2中的数据……如果您不需要表2中的数据(例如在我的示例中使用ringo)，那么为什么我们需要做这个联合呢?我假设你想要ringo，因此你必须要引用表2中的id。

#2

You could also do this with NOT EXISTS:

你也可以在不存在的情况下这样做:

SELECT *
FROM table1
WHERE
NOT EXISTS
(SELECT 1
 FROM table2
 WHERE table1.id = table2.id
 AND table1.name = table2.name)

#3

;with cte as
 (
  (   SELECT name,id FROM table1
      EXCEPT
      SELECT name,id FROM table2)  
       UNION ALL
  (   SELECT name,id FROM table2
      EXCEPT
      SELECT name,id FROM table1)
 )
 Select * 
 from table1 as tbl1
 where
    tbl1.id = cte.id
     and tbl1.name = cte.name

#1