在两个表中从同一列中选择惟一的值。

I'm just playing with SQL and want to achieve the following result I have the following tables:

我只是在使用SQL，想要实现以下结果，我有以下表格:

TABLE_1   ID   NAME
          1    CAR
          2    ANIMAL
          5    ROCK

TABLE_2   ID   NAME
          1    GRASS
          2    ROCKET
          3    STONE
          4    DOG

I want my query to return unique ID values from both tables:

我希望我的查询从两个表返回唯一的ID值:

ID
3
4
5

I have tried using DISTINCT and FULL OUTER JOINS, but without success. Any help would be appreciated.

我尝试过使用不同的和完全的外部连接，但是没有成功。如有任何帮助，我们将不胜感激。

6 个解决方案

#1

You can use UNION ALL , group it and use the HAVING clause :

您可以使用UNION ALL、group it和have子句:

SELECT ID FROM (
    SELECT ID FROM Table_1
    UNION ALL
    SELECT ID FROM Table_2)
GROUP BY ID
HAVING COUNT(*) = 1

#2

Try this:

试试这个:

SELECT ID
FROM (
  SELECT ID
  FROM TABLE_1

  UNION ALL

  SELECT ID
  FROM TABLE_2) AS t
GROUP BY ID
HAVING COUNT(*) = 1

#3

SELECT id FROM table_1 WHERE id NOT IN (SELECT id FROM table_2)
UNION
SELECT id FROM table_2 WHERE id NOT IN (SELECT id FROM table_1)

#4

Try this;)

试试这个。

SELECT ID
FROM(
  SELECT DISTINCT ID FROM TABLE1
  UNION ALL
  SELECT DISTINCT ID FROM TABLE2
) T
GROUP BY ID
HAVING COUNT(ID) = 1

SQLFiddle DEMO

SQLFiddle演示

#5

Construct the intersection manually using UNION. It's easy if you have some unique field in both tables, e.g. ID:

使用UNION手动构造十字路口。如果你在两个表格中都有一些独特的字段，例如ID:

SELECT id FROM Table 1
WHERE id NOT IN (SELECT id FROM Table 2)

UNION

SELECT id FROM Table 2
WHERE id NOT IN (SELECT id FROM Table 1)

#6

-1

SELECT table_1.id
FROM   table_1
WHERE  table_1.id NOT IN (SELECT table_2.id FROM table_2)
UNION
SELECT table_2.id
FROM   table_2
WHERE  table_2.id NOT IN (SELECT table_1.id FROM table_1)

#1