Substring join or additional table, which is faster?
Why Yours Is Better

In general, your pattern is a better idea. 

Performance will depend on indexes, predicate selectivity, and table size though.

The reason your pattern is a better idea comes down to the concept of SARGability, which is a fancy word for if your search arguments can be used as seek predicates, or even fully pushed predicates, (i.e. not being processed in Filter operators after accessing an index).

Some examples of where this can hurt in joins and where clauses are:


function(column) = something
column + column = something
column + value = something
column = @something or @something IS NULL
column like ‘%something%’
column = case when …


When you do stuff like this, your queries can end up with all sorts of bad side effects:


Increased CPU (burn baby burn)
Index Scans (when you could have Seeks)
Implicit Conversion (if your predicates produce a different data type)
Poor Cardinality Estimates (poking the optimizer in the eye)
Inappropriate Plan Choices (because the optimizer is blind now, you jerk)
Long Running Queries (yay job security!)


Better Options

SARGable options for what you're looking for would include:

WHERE [ID] LIKE 'G%'

or

WHERE [ID] &gt;= 'G' AND [ID] &lt; 'H'

An alternative solution would be to add a computed column in just the table you're searching:

ALTER TABLE [Table] 
    ADD Lefty AS Left([ID], 1);

CREATE INDEX ix_whatever 
    ON [Table] (CategoryID , Lefty);


Though like I said before, the performance difference may not be dramatic with smaller tables. 

It's also possible that this index won't be used since your example query is selecting all of the table columns, and this index doesn't cover them. But that's a story for a different day.