What's the syntax for conditional logic in SQL queries?

Data and Databases
1

Hey everyone! I’m trying to figure out how to add some conditional logic to my SQL queries. I want to do something like an if-then statement inside a SELECT.

For instance, I’m working with a product database and I want to create a column that shows if an item is saleable or not. Here’s what I’m thinking:

SELECT 
  CASE 
    WHEN (NotDiscontinued = 1 OR QuantityAvailable > 0) THEN 1 
    ELSE 0 
  END AS CanSell,
  ProductName,
  Price
FROM Inventory

Is this the right way to do it? Are there other methods to add this kind of logic to a query? Any help would be awesome!

2

Your approach using the CASE statement is spot-on for implementing conditional logic in SQL queries. It’s a versatile and widely-used method for handling if-then-like scenarios. Another technique you might find useful is the IIF function, which provides a more concise syntax for simple conditions. For example:

SELECT
IIF(NotDiscontinued = 1 OR QuantityAvailable > 0, 1, 0) AS CanSell,
ProductName,
Price
FROM Inventory

Both CASE and IIF are effective, but CASE offers more flexibility for complex conditions. Remember that the specific syntax might vary slightly depending on your database system, so always check the documentation for your particular SQL flavor.

3

ooh, interesting question! have you considered using subqueries for more complex conditions? like:

SELECT
CASE WHEN EXISTS (SELECT 1 FROM Sales WHERE Inventory.ProductID = Sales.ProductID AND Sales.Date > DATEADD(month, -3, GETDATE())) THEN 1 ELSE 0 END AS RecentlySold,
ProductName,
Price
FROM Inventory

this checks if product was sold in last 3 months. what do you think?

4

ur right, CASE works great! another option is using logical operators directly:

SELECT
(NotDiscontinued = 1 OR QuantityAvailable > 0) AS CanSell,
ProductName,
Price
FROM Inventory

this returns 1 for true, 0 for false. simpler for basic conditions, but CASE is better for complex stuff.