Usage of if function in sql

Usage and code examples of IF function in SQL

The IF function in SQL language is a conditional judgment function, which is used to return different values ​​according to the true or false of the condition. result. It can help us implement some complex logical judgments and data processing operations. Below we will introduce the syntax and usage of the IF function in detail and provide some specific code examples.

The basic syntax of the IF function is as follows:

IF(condition, value_if_true, value_if_false)

Among them, condition is the conditional expression to be judged, value_if_true is the return value when the condition is true, and value_if_false is the return value when the condition is false. . Next, we illustrate the specific usage of the IF function through some practical examples.

Example 1: Determine whether students have passed based on their scores
Suppose there is a student score table "Student", containing the fields "Name" and "Score", and we want to determine whether students have passed based on their scores. If the score is greater than or equal to 60 points, it will be marked as "pass", otherwise it will be marked as "fail".

SELECT Name, Score, 
   IF(Score >= 60, '及格', '不及格') AS Grade
FROM Student;

Example 2: Calculate the discount price based on the order amount
Suppose there is an order table "Orders" containing the fields "Order_ID" and "Amount", and we want to calculate the discount price based on the order amount. If the order amount is greater than or equal to 500, it will be calculated as 20% off; otherwise, it will be calculated as 10% off.

SELECT Order_ID, Amount, 
   IF(Amount >= 500, Amount * 0.8, Amount * 0.9) AS DiscountedPrice
FROM Orders;

Example 3: Determine the operation permissions based on the order status
Suppose there is an order table "Orders" containing the fields "Order_ID" and "Status". We want to determine the user's operation permissions based on the order status. . If the order status is "paid" or "shipped", the user is allowed to return the item; otherwise, it is not allowed.

SELECT Order_ID, Status, 
   IF(Status = '已付款' OR Status = '已发货', '允许退货', '不允许退货') AS Permission
FROM Orders;

Through the above examples, we can see the flexible use of the IF function in SQL. It can help us return different results according to the true or false conditions, thereby realizing complex logical judgments and data processing operations. At the same time, the IF function can also be used with other functions and operators to meet the needs of more practical applications.

Summary:
The IF function is a conditional judgment function in SQL, which is used to return different results according to the true or false conditions. The syntax is IF(condition, value_if_true, value_if_false). Through the IF function, we can implement complex logical judgments and data processing operations. When using the IF function, you need to write the code according to the specific requirements and expression syntax.

The above is an introduction to the usage and code examples of the IF function in SQL. I hope it can provide some help and reference for readers when using the IF function.

The above is the detailed content of Usage of if function in sql. For more information, please follow other related articles on the PHP Chinese website!