Simple SQL Select from 2 Tables (What is a Join?)

问题:

I'm new to SQL. I have a simple problem with getting the results from two different tables.

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

Thanks!

回答1:

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

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.

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

回答2:

You want a JOIN:

SELECT
    u.id,
    username,
    groupname
FROM
    TABLE_USERS AS u
LEFT JOIN TABLE_GROUPS AS g
    ON u.group = g.id