title: Right way to use optional condition in WHERE clause
body: <p>I have an optional condition in <code>WHERE</code> clause in a SQL query. If the parameter <code>opt_y</code> is supplied by user, the condition <code>cond2 &lt;&gt; opt_y</code> is checked, else it's skipped. The other condition (<code>cond1 = x</code>) remains fixed. </p>
  
  <p>Among the following methods, which is more <strong>efficient</strong> &amp; <strong>secure</strong> (avoid SQL injection) . I'm using Django raw query manager to run my SQL (I understand performance depends on the case, but if there's some obvious pitfall, like the db engine can't optimize a particular technique or can't use the cache effectively, please highlight). Ofcourse, if there's a better method, please share it.</p>
  
  <p><strong>Intended query</strong></p>
  
  <pre><code>MyTable.objects.raw("""
    SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
    FROM mytable
    WHERE cond1 = %s AND cond2 &lt;&gt; %s """, [x, opt_y])
  )
  </code></pre>
  
  <p><strong>Method 1:</strong> CASE statement</p>
  
  <pre><code>MyTable.objects.raw("""
    SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
    FROM mytable
    WHERE cond1 = %s AND 
      CASE WHEN %s IS NULL THEN true ELSE cond2 &lt;&gt; %s END""", [x, opt_y, opt_y])
  )
  </code></pre>
  
  <p><strong>Method 2:</strong> Combining strings</p>
  
  <pre><code>query  = """SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
              FROM mytable
              WHERE cond1 = %s """
  params = [x]
  if opt_y:
      q += """AND cond2 &lt;&gt; %s"""
      params.append(opt_y)
  MyTable.objects.raw(query, params)
  </code></pre>
  
  <p><strong>Method 3:</strong> Logical operation</p>
  
  <pre><code>MyTable.objects.raw("""
    SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
    FROM mytable
    WHERE cond1 = %s AND (%s IS NULL OR cond2 &lt;&gt; %s)""", [x, opt_y, opt_y])
  )
  </code></pre>
  
  <p><strong>Update</strong>, adding 1 more method :</p>
  
  <p><strong>Method 4:</strong> This is more of a hack </p>
  
  <pre><code>if not opt_y:
      opt_y = -1 #Or some value that will NEVER appear in a column 
  
  MyTable.objects.raw("""
    SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
    FROM mytable
    WHERE cond1 = %s AND cond2 &lt;&gt; %s""", [x, opt_y])
  )
  </code></pre>
  
  <p>Using Django 1.6 &amp; PostgreSQL 9.3</p>