title: Oracle "Starts With" text join, excluding nulls
body: <p>I am having some trouble. I am trying to build a SQL query that uses "starts with" logic. A little background first...</p>
  
  <p>In the database that I've been tasked to write reports from, there is a "user" table and a "salesperson" table, with salespersons belonging to a user. In a not-so-brilliant move, the designer of the database decided to associate the salespersons through a substring match to their employee code. For example:</p>
  
  <p>John Smith's "employee_code" would be "JS". But he has multiple "salespersons" to distinguish his different sale types. So he might have "JS1", "JS2", "JS3", etc., as his "salesperson_code".</p>
  
  <p>To illustrate:</p>
  
  <pre><code>user table:
  |----------|-----------|----------|---------------|
  | username | firstname | lastname | employee_code |
  |----------|-----------|----------|---------------|
  | JSMITH   | John      | Smith    | JS            |
  |----------|-----------|----------|---------------|
  
  salesperson table:
  |------------------|------------------|
  | salesperson_name | salesperson_code |
  |------------------|------------------|
  | John Smith 1     | JS1              |
  | John Smith 2     | JS2              |
  | John Smith 3     | JS3              |
  |------------------|------------------|
  </code></pre>
  
  <p>There is no foreign key on the salesperson table linking them to the user table, only the substring from the employee code.</p>
  
  <p>I do not remember where I found this answer, but in my queries I've been doing this:</p>
  
  <pre><code>select user.name 
  from user user
  inner join salesperson spn on spn.salesperson_code like user.employee_code || '%'
  </code></pre>
  
  <p>This logic successfully does the "starts with" match. However, there are users with blank employee codes and they, also, match this query.</p>
  
  <p>What I am looking for: <strong>how do I modify this query so that if the employee_code is blank it will not match?</strong> I'm pretty newbie with Oracle queries. Other DBMS' have a starts with clause that will not match blank fields.</p>
  
  <p>Thank you in advance for your help!</p>