Build a Query in Two Phases: Organize First, Itemize Second

Build a query in two phases, and most of your errors disappear before they start. Phase one is to organize: get the tables, joins and filters working with SELECT *. Phase two is to itemize: replace the star with the column list you want.

The Sample Tables

Four branches and seven staff members give us something to join. Boise has no staff on purpose. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Branch (BranchID INT PRIMARY KEY, City VARCHAR(20) NOT NULL, State CHAR(2) NOT NULL);
CREATE TABLE Staff (StaffID INT PRIMARY KEY, FirstName VARCHAR(20) NOT NULL, LastName VARCHAR(20) NOT NULL, BranchID INT NOT NULL);
INSERT INTO Branch (BranchID, City, State) VALUES (1, 'Seattle', 'WA'), (2, 'Spokane', 'WA'), (3, 'Portland', 'OR'), (4, 'Boise', 'ID');
INSERT INTO Staff (StaffID, FirstName, LastName, BranchID) VALUES
  (1, 'Avery', 'Lin', 1), (2, 'Casey', 'Moore', 1), (3, 'Jordan', 'Ellis', 1), (4, 'Riley', 'Park', 2),
  (5, 'Morgan', 'Reed', 2), (6, 'Quinn', 'Hayes', 3), (7, 'Sam', 'Ortiz', 3);
GO

Why the Column List Comes Last

The column list is the first thing you type, and it names columns from tables you haven’t joined yet. Write it too early and the first run fails. This query lists staff columns but reads only the Branch table.

SELECT FirstName, LastName, City, State FROM Branch;

SQL Server returns Msg 207 twice, once for FirstName and once for LastName. SELECT * can’t produce that error. It takes whatever columns the tables have. Line one is always safe, so you can focus on the harder part.

Phase One: Organize

Resist the urge to type the final column list early. The star costs nothing now, and it keeps your attention on the joins.

Start with one table and check it works. Add the join and check again. Add the filter last. Each query below builds on the one before it.

SELECT * FROM Branch;

SELECT *
FROM Branch
INNER JOIN Staff ON Branch.BranchID = Staff.BranchID;

SELECT *
FROM Branch
INNER JOIN Staff ON Branch.BranchID = Staff.BranchID
WHERE State = 'WA';

The row counts are 4, 7 and 5. After each step, ask whether the count makes sense. This is what “working” means: no error, and the rows you expect. There are seven staff members, so the join should return seven rows. A count query confirms it.

SELECT (SELECT COUNT(*) FROM Staff) AS StaffRows,
  (SELECT COUNT(*) FROM Branch INNER JOIN Staff ON Branch.BranchID = Staff.BranchID) AS JoinRows;

Both counts are 7. Every staff row found its branch. Then the filter keeps only the five rows for Washington.

Phase Two: Itemize

Once the star version runs, change the first line to the columns you need. Nothing else moves.

SELECT FirstName, LastName, City, State
FROM Branch
INNER JOIN Staff ON Branch.BranchID = Staff.BranchID
WHERE State = 'WA';

That returns five rows with four columns. If the list includes a column that exists in both tables, SQL Server asks you to choose.

SELECT BranchID, FirstName, City
FROM Branch
INNER JOIN Staff ON Branch.BranchID = Staff.BranchID
WHERE State = 'WA';

The answer is Msg 209, ambiguous column name BranchID. Give each table an alias and put it in front of the column. This is also the moment to add the alias to every column, which makes the query easier to read.

SELECT b.BranchID, s.FirstName, b.City
FROM Branch AS b INNER JOIN Staff AS s ON b.BranchID = s.BranchID
WHERE b.State = 'WA';

When Square Brackets Are Required

Brackets belong in phase two as well, because the column list is where a keyword shows up as a name. Order is a keyword, so an unbracketed table of that name fails.

CREATE TABLE Order (OrderID INT, Note VARCHAR(10));

Wrap the name in square brackets and it works. Names with spaces need them too.

CREATE TABLE #Brackets ([Order] INT, [Project Name] VARCHAR(10));
SELECT [Order], [Project Name] FROM #Brackets;

Not every keyword needs brackets, though. A reader asked what State does in T-SQL. It’s an ordinary column name here, and the query above uses it without brackets. I tried 14 words as column names. Date, Level, Name, State, Status and Type worked. Order, Group, Key, Percent, Select, Table, User and Index failed. Brackets are safe on any name, so many people use them on every one.

Clean Up

Remove the sample tables when you finish.

DROP TABLE Staff;
DROP TABLE Branch;

What to Remember

Organize first: tables, joins and filter with SELECT *, checking the row count at each step. Itemize last: write the column list and prefix each column with an alias. Don’t leave SELECT * in a finished query that other people will run.

To go further, read SELECT * in Production Code: The Hidden Costs, Replace SELECT * with Column Names in SQL Server: SQL in Sixty Seconds #017 and Introduction to JOINs in SQL Server: INNER, OUTER and CROSS JOIN.

A query is not written top to bottom, it is built from the tables outward.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.