简单的 SQL 从 2 个表中选择(什么是联接?) [英] Simple SQL Select from 2 Tables (What is a Join?)
问题描述
我是 SQL 的新手.我在从两个不同的表中获取结果时遇到了一个简单的问题.
I'm new to SQL. I have a simple problem with getting the results from two different tables.
我在一个数据库中有两个表.第一个表有一个带有 id 引用的列,它对应于第二个表中的行.我需要执行什么 SELECT 才能得到一个结果,使得 id 被第二个表中的所有值重新分配.将我正在讨论的表格可视化:
I have two tables in a database. The first table has a column with an id reference, which corresponds to rows in the second table. What SELECT do I need to perform to get a result such that the ids are repalced by all of the values in the second table. To visualize the tables I am discussing:
TABLE_USERS
===========
id username group
-- -------- -----
1 jim A
2 alice A
3 brandon B
TABLE_GROUPS
============
id groupname members
-- --------- -------
A designer 134
B photographer 39
DESIRED_SELECTION
=================
id username group
-- -------- -----
1 jim designer
2 alice designer
3 brandon photographer
谢谢!
推荐答案
你这样做,其实是想JOIN这两个表:
You do, in fact, want to JOIN the two tables:
SELECT * FROM
TABLE_USERS LEFT JOIN TABLE_GROUPS
ON TABLE_USERS.group = TABLE_GROUPS.id
连接表的技巧是在两个表中找到必须匹配的值,并使用on
告诉SQL匹配它们.这个表有一个 ID 列让你这样做 = 你将加入表,ON
,然后列出需要相等的值.
The trick of joining tables is to find the values that must match in the two tables, and use the on
to tell SQL to match them. This table has a ID column to let you do that = you will join the table, ON
, and then list the values that need to be equal.
如果您不想要两个表中的所有列,您可以简单地仅列出您在最终查询中需要的列.这意味着您列出所需的列而不是 Select *
.如下图,如果两个表中出现同名的列,就需要在表名前面加上,让SQL知道你想要哪个值.
If you do not want all of the columns in both tables, you can simply list only the columns you need in your final query. This means that instead of Select *
, you list the columns you want. As shown below, if a column appears with the same name in both tables, you need to prepend the table name, so that SQL know which value you want.
SELECT TABLE_USERS.ID, Username, Groupname
FROM TABLE_USERS
LEFT JOIN TABLE_GROUPS
ON TABLE_USERS.group = TABLE_GROUPS.id
这篇关于简单的 SQL 从 2 个表中选择(什么是联接?)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!