如何关联两张数据表？请求协助关联DNIS.numbers与DNIS.owners表

Hey there! Let's break down how to pull the exact fields you need by linking these two tables. The core of this task is using a SQL JOIN clause to connect the tables via their related fields: OwnerId from DNIS.numbers and ID from DNIS.owners. Here are the most practical solutions tailored to your request:

1. INNER JOIN (Return only matching records) #

This will return rows where there's a valid, corresponding owner entry in DNIS.owners for a number in DNIS.numbers—so only records with a confirmed link between the two tables show up.

SELECT 
    n.Number, 
    n.OwnerId, 
    o.ID, 
    o.Name
FROM 
    DNIS.numbers AS n
INNER JOIN 
    DNIS.owners AS o
ON 
    n.OwnerId = o.ID;

What this does: #

• We use aliases (n for numbers, o for owners) to keep the query clean and avoid confusion between fields.
• The ON clause defines the critical relationship: it matches the OwnerId in the numbers table to the unique ID in the owners table.
• We explicitly select the exact fields you requested from each table, no extra fluff.

2. LEFT JOIN (Return all numbers, even without a matching owner) #

If you need to retain every number from DNIS.numbers—even if there's no corresponding owner in DNIS.owners (owner fields will show as NULL for these entries)—use a LEFT JOIN instead:

SELECT 
    n.Number, 
    n.OwnerId, 
    o.ID, 
    o.Name
FROM 
    DNIS.numbers AS n
LEFT JOIN 
    DNIS.owners AS o
ON 
    n.OwnerId = o.ID;

Quick Tips: #

• Double-check that OwnerId (from numbers) and ID (from owners) have compatible data types (e.g., both integers) to ensure the join works as expected.
• While you could skip table aliases if field names are unique, using them makes your query more readable and resilient to future schema changes.

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