Conditional SQL where clause

Question

I am storing logos in a varbinary(max) field. I'm looking for an SQL statement that says,

select logo where customerid=5

but if the logo for a particular customerid is NULL, then,

select logo where customerid=18

which is the default logo. I'd like to do this in a single query if possible. Can this be done? Thanks.

It's MSSQL, not sure why my question was altered from "if it's null" to "if that customerid doesn't exist", the customerid does exist but they might not have added a logo, leaving the field null — Greg
– Greg, Commented Jan 31, 2013 at 22:06

Bill Karwin · Accepted Answer · 2013-01-31 21:58:36Z

4

SELECT COALESCE(b.logo, dflt.logo) AS logo 
FROM mytable dflt
LEFT OUTER JOIN mytable b ON b.customerid=5
WHERE dflt.customerid=18

answered Jan 31, 2013 at 21:58

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Joel Coehoorn Over a year ago

I think both of our answers work, but I'd be curious to see which of our answers performs better... on the one hand, I'm skeptical of the join. On the other hand, I like getting to use coalesce and I'm not sure my IN() + ORDER BY CASE would be any better, especially as those don't work well with indexes but your join conditions do.

Joel Coehoorn Over a year ago

Also, yours is more portable, you're not having to convert between TOP 1, LIMIT 1, etc.

Bill Karwin Over a year ago

Yeah I don't have MS SQL Server handy so I can't test which performs better. If customerid is the primary key (or clustered key) then I assume it'll match 1 or 2 rows very efficiently, so either solution will be quick.

Greg Over a year ago

This works, what I don't get is how you join tables that don't exist. What makes b and dflt copies of mytable?

Bill Karwin Over a year ago

The b and dflt are sometimes called table aliases. These are not copies of the table -- think of them more like a "label" for a subset of rows in mytable. By joining these rows, we create a new virtual row which is one from each alias arranged side-by-side. This allows an expression in the SELECT-list to reference one row from each alias.

Joel Coehoorn · Accepted Answer · 2013-01-31 21:57:14Z

2

SELECT TOP 1 logo
FROM [Table]
WHERE logo is not null and customerid IN (@CustomerID, 18)
ORDER BY CASE WHEN customerid= 18 THEN 1 ELSE 0 END

Note how I handled the order. I suspect you may have some customers with an ID of <18 and some customers with an id >18, and so you need to be careful here.

answered Jan 31, 2013 at 21:57

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

3 Comments

Greg Over a year ago

This works! Thank you! I just chose this one because I still don't understand joins very well, but thanks to all.

Bill Karwin Over a year ago

Programming in SQL without understanding joins is like programming in Java or C# or other language without understanding while loops.

Greg Over a year ago

I know, I've been staring at your solution for a half hour trying to figure it out, it works too.

Collectives™ on Stack Overflow

Conditional SQL where clause

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related