1052:字段列表中的列“id"不明确 [英] 1052: Column 'id' in field list is ambiguous

问题描述

我有两张桌子.tbl_names 和 tbl_section 都包含 id 字段.我如何选择 id 字段，因为我总是收到此错误:

1052: 字段列表中的列 'id' 不明确

这是我的查询:

SELECT id, name, section从 tbl_names, tbl_section哪里 tbl_names.id = tbl_section.id

我可以选择所有字段并避免错误.但这会浪费性能.我该怎么办?

解决方案

SQL 支持通过在引用前加上全表名来限定列:

SELECT tbl_names.id, tbl_section.id, name, section从 tbl_names在 tbl_section.id = tbl_names.id 上加入 tbl_section

...或表别名:

SELECT n.id, s.id, n.name, s.sectionFROM tbl_names n加入 tbl_section s ON s.id = n.id

表别名是推荐的方法——为什么要输入更多的内容?

为什么这些查询看起来不同?

其次，我的回答使用 ANSI-92 JOIN 语法(你的是 ANSI-89).虽然它们执行相同的操作，但 ANSI-89 语法不支持外部连接(RIGHT、LEFT、FULL).ANSI-89 语法应该被视为已弃用，SO 上有很多人不会投票支持 ANSI-89 语法来加强这一点.如需更多信息，请参阅此问题.>

I have 2 tables. tbl_names and tbl_section which has both the id field in them. How do I go about selecting the id field, because I always get this error:

1052: Column 'id' in field list is ambiguous

Here's my query:

SELECT id, name, section
  FROM tbl_names, tbl_section 
 WHERE tbl_names.id = tbl_section.id

I could just select all the fields and avoid the error. But that would be a waste in performance. What should I do?

解决方案

SQL supports qualifying a column by prefixing the reference with either the full table name:

SELECT tbl_names.id, tbl_section.id, name, section
  FROM tbl_names
  JOIN tbl_section ON tbl_section.id = tbl_names.id 

...or a table alias:

SELECT n.id, s.id, n.name, s.section
  FROM tbl_names n
  JOIN tbl_section s ON s.id = n.id 

The table alias is the recommended approach -- why type more than you have to?

Why Do These Queries Look Different?

Secondly, my answers use ANSI-92 JOIN syntax (yours is ANSI-89). While they perform the same, ANSI-89 syntax does not support OUTER joins (RIGHT, LEFT, FULL). ANSI-89 syntax should be considered deprecated, there are many on SO who will not vote for ANSI-89 syntax to reinforce that. For more information, see this question.

这篇关于1052:字段列表中的列“id"不明确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！