SQL SERVER: Conditional selects

In a recent project I needed to come up with a way to return information from one of two tables, based on whether or not the value of the first column was null.

In situations like this,  the SELECT CASE statement is the best option.

This is the syntax:


(

SELECT CASE WHEN

[condition]

THEN

[return if true]

ELSE

[return if false]

END

)

Its important to know that you can use SELECT CASE in both the selects AND the WHERE CLAUSES of a sql statement.

Here is a full query,  where,  if the person’s address is null in table a,  it will provide the address from table b.

You would use the same syntax in the WHERE CLAUSE.


SELECT

a.name as Name,

(

SELECT CASE

WHEN

a.address IS NULL

THEN

b.address

ELSE

a.address

END

) as Address

FROM

users AS a

INNER JOIN

userdata AS b ON a.UID = b.UID

Advertisements

2 thoughts on “SQL SERVER: Conditional selects

  1. Have you considered using COALESCE in this case? It provides an n-ary shorthand for choosing the first non-null in a list of expressions.

    Like

    • Thanks for the suggestion. Someone mentioned it on the Reddit thread, which was the first I’ve heard of it.
      Its been a long time since I’ve done much with SQL Server, but new projects at work has brought me back to it.

      I’m not sure if, from what I’ve read, if COALESCE will work though….the actual queries are quite complex.

      I will look into it thought.

      +1 for the suggestion!

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s