在SQL中查询多个不相关的表

I have more than 50 tables that are not related but all of them have a "Name" field, I want to query "John" and get all Johns in the different tables and store each row in an array (Javascript) for example:

我有超过50个不相关的表,但它们都有一个“名称”字段,我想查询“John”并在不同的表中获取所有Johns并将每行存储在一个数组(Javascript)中,例如:

arr['table1']="results if any"
arr["table2"]="results if any".

What I'm doing right now is a for loop for each table:
SELECT * from tablesNameArray[i] WHERE name="John",
but I'm really wondering if there is any other better or "more" correct way to do it.

我现在正在做的是每个表的for循环:SELECT * from tablesNameArray [i] WHERE name =“John”,但我真的想知道是否还有其他更好或更“正确”的方法来做。

Thanks

2 个解决方案

#1

You can do it in a single query using UNION:

您可以使用UNION在单个查询中执行此操作:

SELECT * FROM table1 WHERE name = 'John'
UNION ALL
SELECT * FROM table2 WHERE name = 'John'
UNION ALL
SELECT * FROM table3 WHERE name = 'John'
...

You can construct the query dynamically from the array:

您可以从数组动态构造查询:

sql = tablesNameArray.map(table => `SELECT * FROM ${table} WHERE name = 'John'`).join(" SELECT ALL ");

#2

You could use joins. See the join docs, MySQL in this case.

你可以使用连接。在这种情况下,请参阅连接文档,MySQL。

Something like this should do it:

这样的事情应该这样做:

SELECT table1.*, table2.*, table3.*
FROM table1
LEFT JOIN table2 ON table1.name = table2.name
LEFT JOIN table3 ON table1.name = table3.name
WHERE table1.name = "John";

A left join will still select from the first table even if the joined table doesn't have a matching row -- you'll get NULL in the second table's selected columns for those rows which didn't match.

即使连接表没有匹配的行,左连接仍将从第一个表中进行选择 - 对于那些不匹配的行,您将在第二个表的选定列中获得NULL。

However, depending on your requirements, this is possibly no good -- I believe it won't grab rows from table2 or table3 where there's no corresponding row for that user in table1.

但是,根据您的要求,这可能并不好 - 我相信它不会从table2或table3中获取行,而table1中的该用户没有相应的行。

As pointed out by @spencer7593 in a comment below, you should probably only do this if you're certain the name column is unique within each table, otherwise you could be generating some ridiculously huge result sets.

正如@ spencer7593在下面的评论中指出的那样,如果你确定name列在每个表中是唯一的,你应该只这样做,否则你可能会生成一些非常庞大的结果集。

#1