SQL - Expressions

An SQL expression is a combination of one or more values, operators, and SQL functions that resolves to a value. Expressions are the building blocks of queries, used in SELECT lists, WHERE clauses, GROUP BY clauses, and more.

What is an SQL Expression?

Think of an expression as a formula. It can be as simple as a constant value (100) or a column name (salary), or as complex as a combination of functions, calculations, and subqueries. They are commonly used in the WHERE clause to filter data.

SELECT column1, column2, ...
FROM table_name
WHERE [EXPRESSION]; -- A condition that evaluates to TRUE, FALSE, or UNKNOWN

Boolean Expressions

Boolean expressions evaluate to a truth value: TRUE, FALSE, or UNKNOWN (if NULL is involved). They typically use comparison operators (=, >, <, <>) and logical operators (AND, OR, NOT) to form conditions.

Consider the CUSTOMERS table:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00

The following query uses a boolean expression to find customers who are 25 years old AND have a salary greater than 2000:

SELECT * FROM CUSTOMERS
WHERE AGE = 25 AND SALARY > 2000;

Output

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00

Numeric Expressions

Numeric expressions perform mathematical calculations using arithmetic operators (+, -, *, /, %). They can be used on numeric literals or columns.

Example: Calculating a Derived Value

Here, a numeric expression calculates a 10% bonus for each customer and gives the resulting column an alias BONUS.

SELECT NAME, SALARY, SALARY * 0.10 AS BONUS
FROM CUSTOMERS;

Output

NAME	SALARY	BONUS
Ramesh	2000.00	200.00
Khilan	1500.00	150.00
kaushik	2000.00	200.00
Chaitali	6500.00	650.00

Date and Time Expressions

These expressions operate on date and time data types. They are used to retrieve the current time, manipulate dates, or filter records based on a time period.

Example: Using Date Functions

Most databases provide functions to get the current timestamp (CURRENT_TIMESTAMP, NOW()) and to extract parts of a date (EXTRACT, DATE_PART).

-- Get the current system timestamp (syntax may vary slightly)
SELECT CURRENT_TIMESTAMP;

-- Example for PostgreSQL or Standard SQL: Get the year from an order date
SELECT OID, EXTRACT(YEAR FROM ORDER_DATE) AS OrderYear
FROM ORDERS
WHERE ORDER_DATE < DATE '2023-11-01';

Note the use of DATE '2023-11-01'. This is the standard SQL syntax for a date literal and is better practice than using a plain string like '2023-11-01', which relies on implicit casting.

String Expressions

String expressions are used to manipulate character data. Common operations include concatenation and using functions like UPPER, LOWER, SUBSTRING, and TRIM.

Example: Concatenating and Formatting Strings

The following query creates a full name by concatenating first and last names and converts the address to uppercase. The concatenation operator is || in standard SQL and PostgreSQL, while it’s + in SQL Server.

-- Standard SQL / PostgreSQL
SELECT
    NAME || ' from ' || ADDRESS AS FullDescription,
    UPPER(ADDRESS) AS UppercaseAddress
FROM CUSTOMERS;

Conditional Expressions (CASE)

The CASE expression is a powerful tool for implementing if-then-else logic within an SQL query. It allows you to return different values based on a set of conditions.

Example: Categorizing Data

This query uses a CASE expression to categorize customers into salary brackets.

SELECT NAME, SALARY,
    CASE
        WHEN SALARY < 2000 THEN 'Low'
        WHEN SALARY >= 2000 AND SALARY < 5000 THEN 'Medium'
        WHEN SALARY >= 5000 THEN 'High'
        ELSE 'Uncategorized'
    END AS SalaryBracket
FROM CUSTOMERS;

Output

NAME	SALARY	SalaryBracket
Ramesh	2000.00	Medium
Khilan	1500.00	Low
kaushik	2000.00	Medium
Chaitali	6500.00	High