oracle 单个 SQL 查询以检查任一表是否包含 column=x 的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2231717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Single SQL query to check if either table contains a row with column=x
提问by benstpierre
I have 2 unrelated tables A and B which both have foreign key constraints on C. I need to run an sql query that determines if either A or B contain a given id of C. My first approach was to use the union all but A and B are not related thus it will not work.
我有 2 个不相关的表 A 和 B，它们都对 C 有外键约束。我需要运行一个 sql 查询来确定 A 或 B 是否包含给定的 C id。我的第一种方法是使用联合，除了 A 和B 不相关，因此它不起作用。
Any ideas?
有任何想法吗？
采纳答案by Steve Homer
Not 100% sure what you're asking but if you mean to return the elements of A and B that match the id in C then this will do it.
不是 100% 确定你在问什么，但如果你的意思是返回与 C 中的 id 匹配的 A 和 B 的元素，那么这会做到。
Select c.*, a.*, b.*
From c.id
Left Outer Join a On a.id = c.id
Left Outer Join b On b.id = c.id
Where c.id = @somevalue and (a.id Is Not Null or b.id Is Not Null)
Where @somevalue is the value you're looking for.
其中@somevalue 是您要查找的值。
回答by Matthew Flynn
Select 1
From DUAL
Where Exists ( Select null From Table_A Where a.fk = :id ) OR
Exists ( Select null From Table_B Where b.fk = :id );
回答by Erik A. Brandstadmoen
You could indeed use union, why not? But why use UNION ALL, and not just UNION? Just pick the one common column:
你确实可以使用联合，为什么不呢？但是为什么要使用 UNION ALL 而不仅仅是 UNION？只需选择一个常见的列：
SELECT 1
FROM
(select A.fk from A inner join C on A.FK = C.pk
UNION
select B.fk from B inner join C on B.FK = C.pk) AS bothTables
WHERE fk = 'desiredValue';
This would work just nicely.
这会很好地工作。
Tested it on the following tables in MySQL, with myValue = 1, just to verify.
在 MySQL 中的以下表上进行了测试，其中 myValue = 1，只是为了验证。
mysql> select * from A;
+------+--------+------+
| pk | value | fk |
+------+--------+------+
| 1 | ape | 2 |
| 2 | fjfjfj | 3 |
+------+--------+------+
2 rows in set (0.00 sec)
mysql> select * from B;
+------+--------+------+
| pk | value | fk |
+------+--------+------+
| 1 | katt | 1 |
| 2 | fjfjfj | 3 |
+------+--------+------+
2 rows in set (0.00 sec)
mysql> select * from C;
+------+-------+
| pk | value |
+------+-------+
| 1 | hei |
| 2 | nei |
| 3 | jeg |
+------+-------+
3 rows in set (0.00 sec)
