mysql-无法使用select *获取所有列 [英] mysql - Unable to get all columns with select *

问题描述

我有以下2个表t1，t2

I have following 2 tables t1, t2

CREATE TABLE t1 (
id INT PRIMARY KEY
);

CREATE TABLE t2 (
id INT PRIMARY KEY
);

INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);

我在跑步

select * from t1 left join t2 using(id);

结果:

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

关于运行脚本:

select t1.id, t2.id from t1 left join t2 using(id);

结果:

+----+------+
| id | id   |
+----+------+
|  1 | NULL |
|  2 |    2 |
|  3 |    3 |
+----+------+

select *应该返回所有列，所以，为什么在使用select *时我没有得到2行?

select * is supposed to return all the columns, so, why I am not getting 2 rows when I am using select *?

注意:我正在使用Mysql

Note: I am using Mysql

推荐答案

如文件所述:

自然联接和使用USING的联接，包括外部联接变体，均根据SQL:2003标准进行处理:

Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard:

NATURAL联接的冗余列不出现.考虑以下这组语句:

Redundant columns of a NATURAL join do not appear. Consider this set of statements:

CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
SELECT * FROM t1 JOIN t2 USING (j);

第j列是在USING子句中命名的，在输出中应该只出现一次，而不是两次.

column j is named in the USING clause and should appear only once in the output, not twice.

这篇关于mysql-无法使用select *获取所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！