SQL关联查询问题：如何从Owner和Condo_Unit表获取指定字段？

Hey there! No worries at all—joining tables with shared column names is totally normal when you're just starting out with SQL, so let's walk through this clearly.

核心问题：明确指定列所属的表 #

Since both your Owner and Condo_Unit tables have an OwnerNum column, you need to tell SQL which one you want to pull (though in this case, the values should match because we're joining on this column). You do this by prefixing the column name with either the full table name or a shorthand alias.

方法1：使用完整表名前缀 #

This is straightforward and easy to read for beginners:

SELECT Owner.OwnerNum, Owner.LastName, Condo_Unit.LocationNum
FROM Owner
-- Join the two tables where their OwnerNum values match
JOIN Condo_Unit ON Owner.OwnerNum = Condo_Unit.OwnerNum;

Here, Owner.OwnerNum explicitly tells SQL to grab the OwnerNum from the Owner table. The JOIN clause links the two tables together so we can pair each owner with their corresponding condo unit location.

方法2：使用表别名（更简洁） #

As your queries get longer, aliases make code cleaner. You can assign short aliases to tables right after their names in the FROM/JOIN clauses:

SELECT o.OwnerNum, o.LastName, cu.LocationNum
FROM Owner o
JOIN Condo_Unit cu ON o.OwnerNum = cu.OwnerNum;

Here, o stands for Owner and cu stands for Condo_Unit—the logic is exactly the same as the first query, just shorter to write.

Bonus: Include all owners (even those without a condo unit) #

If you want to see every owner in your Owner table, even if they don't have a matching entry in Condo_Unit, use a LEFT JOIN instead. For these owners, the LocationNum will show up as NULL:

SELECT o.OwnerNum, o.LastName, cu.LocationNum
FROM Owner o
LEFT JOIN Condo_Unit cu ON o.OwnerNum = cu.OwnerNum;

内容的提问来源于Stack Exchange，提问作者Harry Brown