如何用SQL将3张表的数据合并展示为一张表？

Hey there! Let's figure out how to get the SQL query you need, depending on how your three tables are related to each other.

1. Match rows by the shared Name column (most common use case) #

If the Name field is a common identifier across all tables (like product names that exist in A, B, and C), you'll want to join the tables on Name to align corresponding rows. This ensures you're pairing the right records together:

SELECT 
    A.Name AS A_name,
    A.Stock AS A_stock,
    B.Name AS B_name,
    B.Stock AS B_stock,
    C.Name AS C_name,
    C.Stock AS C_stock
FROM A
INNER JOIN B ON A.Name = B.Name
INNER JOIN C ON A.Name = C.Name;

• Use INNER JOIN if you only want rows where the same Name exists in all three tables.
• If you need to include rows from one table even when there's no matching Name in the others, swap INNER JOIN with LEFT JOIN instead.

2. No relationship between tables (Cartesian product) #

If you just want every possible combination of rows from A, B, and C (note: this will generate a huge number of rows unless your tables are tiny), use a cross join:

SELECT 
    A.Name AS A_name,
    A.Stock AS A_stock,
    B.Name AS B_name,
    B.Stock AS B_stock,
    C.Name AS C_name,
    C.Stock AS C_stock
FROM A CROSS JOIN B CROSS JOIN C;

3. List all rows from each table separately (with nulls for missing tables) #

If you want to show all rows from A, B, and C in a single result set (each row will have data from one table and null values for the other two), use UNION ALL:

SELECT 
    Name AS A_name,
    Stock AS A_stock,
    NULL AS B_name,
    NULL AS B_stock,
    NULL AS C_name,
    NULL AS C_stock
FROM A
UNION ALL
SELECT 
    NULL AS A_name,
    NULL AS A_stock,
    Name AS B_name,
    Stock AS B_stock,
    NULL AS C_name,
    NULL AS C_stock
FROM B
UNION ALL
SELECT 
    NULL AS A_name,
    NULL AS A_stock,
    NULL AS B_name,
    NULL AS B_stock,
    Name AS C_name,
    Stock AS C_stock
FROM C;

Pick the approach that best fits your actual data and what you're trying to achieve!

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