如何使JOIN查询结果列带原表前缀，模拟单表输出？

Let's fix your query step by step—there are two key issues in your original code that are blocking you from getting the desired result:

1. Fix the Missing Join Key in Your Subquery #

Your subquery only selects lastname, but you need to include firstname_id to link it with firstnametable. Without this column, the database has no way to match rows between the two tables, which would throw an error.

2. Assign the Prefixed Column Alias Correctly #

To get a column name like firstnametable.lastname, you need to wrap the alias in quotes (syntax varies slightly by database). The dot is a special character databases use to separate tables and columns, so wrapping the alias tells the database to treat it as a single column name.

Corrected Query (MySQL Example) #

SELECT 
  firstnametable.id,
  firstnametable.firstname,
  jointable.lastname AS `firstnametable.lastname`
FROM firstnametable
LEFT JOIN (
  SELECT firstname_id, lastname 
  FROM lastnametable 
  WHERE active = 1
) AS jointable ON firstnametable.id = jointable.firstname_id

Notes for Other Databases #

• For PostgreSQL or Oracle: Replace backticks with double quotes ("firstnametable.lastname").
• For SQL Server: Use square brackets instead ([firstnametable.lastname]).
• The CONCAT in your original query was unnecessary—we can directly select the filtered lastname value from the subquery.

This query will return exactly the result set you want: matching active last names to their corresponding first names, with the lastname column labeled as firstnametable.lastname.

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