简单的 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屋！