title: T-SQL Optimized conditional join
body: <p>Hey guys it's Brian from OMDbAPI.com</p>
  
  <p>I hit a little speed bump when trying to use a single query for both Movie and Episode data. I recently started collecting additional Episode details in a separate table (being only two new columns have been added, Season #/Episode #) I put them in a separate table because those columns would be null in my main table 90% of the time but the other columns do work across movies/episodes (title/rating/release date/plot etc.)</p>
  
  <p>So I'm trying to use a single query for returning Movie data but if the ID has a type = 'episode' return the additional fields from the other table. Problem is I don't know that ID is an episode until it's queried, and the least amount of calls to the database (smaller execution plan) the better, as this is called hundreds of times per second (currently 25+ million requests a day)</p>
  
  <p>I created a small <a href="http://sqlfiddle.com/#!3/e2253/7" rel="nofollow">SQL Fiddle</a> of what I'm trying to achieve.</p>
  
  <p>My question is what is the best method with the least performance cost to show these fields if it's an episode and completely suppress them if not? Is Dynamic SQL my only option? Thanks.</p>