SQL Sub Query

I was having some problem with a SQL query few hour ago, it required me to write a sub query to execute it correctly.

Hmm, not good, heavy process.

If & Else If

These two set of codes seem like having no different. It seem like "return" make "else if" useless.