从具有不同列数的两个表中选择*

How would I select different columns from two different tables, such as:

如何从两个不同的表中选择不同的列，例如：

SELECT username, email FROM `table1` 
UNION
 SELECT * FROM `table2` WHERE username = 'user1';

I'm getting an error "#1222 - The used SELECT statements have a different number of columns". From what I understand UNION will not work,

我收到错误“＃1222 - 使用的SELECT语句具有不同的列数”。根据我的理解，UNION不起作用，

Is there a way to accomplish this, since I would need unequal number of columns and rows and there are no mutual/similar entries in the two tables (i.e. user1 is not listed in table1)?

有没有办法实现这一点，因为我需要不等数量的列和行，并且两个表中没有相互/相似的条目（即user1未在表1中列出）？

Can this not be done in one query?

这可以不在一个查询中完成吗？

thank you!

谢谢！

3 个解决方案

#1

You can fake the missing columns using an alias - e.g.

您可以使用别名伪造丢失的列 - 例如

 SELECT username, email, '' as name FROM `table1` 
 UNION
 SELECT username, email, name FROM `table2` 
 WHERE username = 'user1';

where name is in table2, but not in table1

name在table2中，但不在table1中

Unless you're confusing UNIONS with JOINS:

除非你把UNIONS与JOINS混淆：

SELECT table1.*, table2.* FROM
table1 INNER JOIN table2
ON table1.username = table2.username

this would merge both tables, so you get all the columns on the same row.

这将合并两个表，因此您可以获得同一行上的所有列。

#2

If there's no mutual or similar entries in the two tables, these should be two different select statements.

如果两个表中没有相互或类似的条目，则这些条目应该是两个不同的select语句。

SELECT username, email FROM `table1`;


SELECT * FROM `table2` WHERE username = 'user1';

What's your motivation for doing otherwise?

这样做的动机是什么？

Are the entries in table2 related to table1? Would a join be more appropriate?

table2中的条目是否与table1相关？加入会更合适吗？

SELECT t1.username, t1.email, t2.*
FROM table1 t1
    JOIN table2 t2 ON t1.username = t2.username
WHERE t1.username = 'user1';

#3

In the table with less columns, try

在列数较少的表中，请尝试

SELECT *, 0 as col1, 0 as col2, ...

etc in order to make them the same number of columns.

等，以使它们具有相同数量的列。

#1