By SpeedOfRound


2019-05-13 17:09:36 8 Comments

I have a case that come up regularly at work. I have many tables that use a 4 character string as a foreign key : G191

The G is a sort of category, the 19 is the year, and the 1 is an instance. Getting all rows of category G is something we do very often. Usually like:

SELECT * FROM [Table] 
WHERE Left([ID], 1) = 'G'

There is a way to get this effect without manipulating a string, by joining to a table where this category is defined:

SELECT * FROM [Table]
JOIN [Categories] ON [Table].CategoryID = [Categories].CategoryID
WHERE [Categories].Letter = 'G'

My co worker insists that the first way is more performant, and rolls his eyes at me for doing it the second way.

Which one is better? Does joining by another table really add more work then checking the first character of a string?

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Evaluating most frequent values of columns in a single query

1 Answered Questions

1 Answered Questions

[SOLVED] CROSS JOIN with later PIVOT going rather slowly

1 Answered Questions

3 Answered Questions

[SOLVED] Which is faster join

2 Answered Questions

3 Answered Questions

[SOLVED] SQL join with multiple columns as FK to same list table

  • 2014-10-03 18:57:08
  • Sabre
  • 1456 View
  • 2 Score
  • 3 Answer
  • Tags:   sql-server join

2 Answered Questions

[SOLVED] Searching multiple tables in SQL

Sponsored Content