连接两张表并创建新表：基于Customer与Family表的技术需求

First, let's recap the two tables we're working with to make sure we're aligned:

Customer Table #

Family Table #

The core link here is Family.Cust_id which maps directly to Customer.ID—this is our join key to combine the tables.

Option 1: Create a New Table with Inner Join (Only Matching Records) #

An inner join will only include rows where a customer has corresponding family records. This means Jack (C2) won't show up in the result since he has no entries in the Family table.

CREATE TABLE CombinedCustomerFamily AS
SELECT
    c.ID AS Customer_ID,
    c.Name AS Customer_Name,
    c.Supervisor,
    f.ID AS Family_Member_ID,
    f.Name AS Family_Member_Name,
    f.Status
FROM Customer c
INNER JOIN Family f ON c.ID = f.Cust_id;

Option 2: Create a New Table with Left Join (Include All Customers) #

If you want to keep every customer—even those without family ties—use a left join. Customers without family records will have NULL values in all family-related columns.

CREATE TABLE CombinedCustomerFamily AS
SELECT
    c.ID AS Customer_ID,
    c.Name AS Customer_Name,
    c.Supervisor,
    f.ID AS Family_Member_ID,
    f.Name AS Family_Member_Name,
    f.Status
FROM Customer c
LEFT JOIN Family f ON c.ID = f.Cust_id;

Sample Results #

For the inner join, your new table will look like this:

For the left join, you'll get an extra row for Jack:

Quick note: If you're using SQL Server, swap CREATE TABLE ... AS SELECT for SELECT ... INTO CombinedCustomerFamily FROM ...—the logic stays identical, only the syntax changes slightly per database.

内容的提问来源于stack exchange，提问作者new_learner