课程ETL作业求助：插入Transform表时获取authorID的子查询WHERE子句

Got it, let's break this down for your first ETL job's transform step. The core here is mapping your source book data (which I assume includes the author's first and last name—since that's how you'll link to the author table) to the correct author.id value for the Transform table.

Basic Subquery Approach #

First, let's say your source data lives in a staging table (common in ETL workflows) called Staging_Books, with columns: Book, ISBN, AuthorFirstName, AuthorLastName. These name fields are what you'll use to match against the author table.

Here's how the INSERT query with a subquery would look, with the critical WHERE clause in the subquery:

INSERT INTO Transform (Book, ISBN, author)
SELECT
  sb.Book,
  sb.ISBN,
  -- Subquery to fetch the correct author ID
  (SELECT a.id
   FROM author a
   WHERE a.FirstName = sb.AuthorFirstName
     AND a.LastName = sb.AuthorLastName) AS author_id
FROM Staging_Books sb;

Key Details About the WHERE Clause #

• You must match both FirstName and LastName in the subquery's WHERE clause. This avoids pulling the wrong author ID if multiple authors share a first or last name.
• If your source data stores the author's full name in a single field (like "John Doe" instead of separate columns), you'll need to split that string first using your SQL dialect's string functions (e.g., SPLIT_PART in PostgreSQL, SUBSTRING + CHARINDEX in SQL Server) before matching.

Alternative: Use JOIN for Better Performance & Readability #

For larger datasets, a JOIN is often more efficient than a subquery, and it's easier to handle edge cases like missing author matches. Here's that version:

INSERT INTO Transform (Book, ISBN, author)
SELECT
  sb.Book,
  sb.ISBN,
  a.id
FROM Staging_Books sb
INNER JOIN author a 
  ON a.FirstName = sb.AuthorFirstName 
  AND a.LastName = sb.AuthorLastName;

• Use INNER JOIN if you only want to insert records where a matching author exists in the author table.
• If you need to keep all source records (even without a matching author), swap to LEFT JOIN and use COALESCE(a.id, [default_value]) to set a fallback for the author field.

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