SQL Server四表查询需求：获取ID=0的各表name字段值

Hey there, let's work through getting your query right. Your original SQL has two critical problems that are preventing it from returning the results you want:

• Ambiguous ID=0 condition: Since all four tables (Job, Home, Games, Example) have an ID column, the database doesn't know which table's ID you're referring to—this will throw a column ambiguity error.
• Implicit Cartesian Product: Using commas to separate tables creates a cross join (all possible combinations of rows from each table), which is not what you want here unless you intentionally need every possible row combination (you don't, in this case).

Based on your expected output (Jean, Robocop, Witcher, Example1), here are the two most common solutions depending on how you want the results formatted:

Scenario 1: Each Name as a Separate Row #

If you want each table's ID=0 name to show up as its own row in the results, use UNION ALL to combine four individual queries:

SELECT name FROM Job WHERE ID = 0
UNION ALL
SELECT name FROM Home WHERE ID = 0
UNION ALL
SELECT name FROM Games WHERE ID = 0
UNION ALL
SELECT name FROM Example WHERE ID = 0;

This will pull the name from each table where ID=0 and stack them into a single result set, giving you four rows (one for each name) if every table has an ID=0 record.

Scenario 2: All Names in a Single Row #

If you want all four names to appear as separate columns in one row, use explicit cross joins (since there's no relationship between the tables, we're just combining their single ID=0 records) and explicitly filter each table's ID:

SELECT 
    Job.name AS Job_Name,
    Home.name AS Home_Name,
    Games.name AS Games_Name,
    Example.name AS Example_Name
FROM Job
CROSS JOIN Home
CROSS JOIN Games
CROSS JOIN Example
WHERE Job.ID = 0 
  AND Home.ID = 0 
  AND Games.ID = 0 
  AND Example.ID = 0;

Or a more modern, readable version using JOIN with an always-true condition (since we want to combine the single records):

SELECT 
    j.name AS Job_Name,
    h.name AS Home_Name,
    g.name AS Games_Name,
    e.name AS Example_Name
FROM Job j
JOIN Home h ON 1=1
JOIN Games g ON 1=1
JOIN Example e ON 1=1
WHERE j.ID = 0 
  AND h.ID = 0 
  AND g.ID = 0 
  AND e.ID = 0;

This will return one row with four columns, each holding the name from the corresponding table's ID=0 record.

Quick Notes #

• Always specify which table an ID comes from (like Job.ID instead of just ID) to avoid ambiguity.
• Explicit JOIN syntax is better than comma-separated tables—it makes your query's intent clearer and easier to maintain.
• If any table might not have an ID=0 record:
  • UNION ALL will just skip that table's result.
  • The cross join approach will return no rows at all (since there's no matching record to join with). Pick the option that fits your data reality.

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