SQL - Expressions

An SQL expression is a combination of one or more values, operators and SQL functions that are all evaluated to a value. These SQL EXPRESSIONs are pke formulae and they are written in query language. You can also use them to query the database for a specific set of data.

Expressions are used in WHERE clause of an SQL query. As you might have already known, a WHERE clause specifies a condition that needs to be satisfied for the purpose of filtering records from a database table. This condition is comprised of either single or multiple expressions. These expressions are further classified into three types −

Boolean Expressions

Numeric Expressions

Date and time Expressions

Let us discuss each of these in detail further in this chapter.

Syntax

Consider the basic syntax of the SELECT statement containing expressions as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];

Boolean Expressions

SQL Boolean Expressions are SQL expressions that only return Boolean Datatype as a result. These expressions can be of two types −

Boolean Expressions that check for equapty of two values using SQL comparison operators. Here, equapty of these values is a condition.

Boolean Expressions can also contain one value paired with an SQL logical operator. In this case, the logic specified acts pke a condition.

They return either TRUE, FALSE or UNKNOWN as the result. If the condition is met, these expressions return TRUE; and FALSE otherwise. UNKNOWN is returned when operands in the expression are NULL values.

Syntax

Following is the syntax −

SELECT column1, column2, columnN 
FROM table_name 
WHERE BOOLEAN EXPRESSION;

Example

Consider the CUSTOMERS table having the following records −

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
7 rows in set (0.00 sec)

The following query is a simple example showing the usage of an SQL Boolean Expression −

SELECT * FROM CUSTOMERS WHERE SALARY = 10000;

Output

The output will be displayed as −

+----+-------+-----+---------+----------+
| ID | NAME  | AGE | ADDRESS | SALARY   |
+----+-------+-----+---------+----------+
|  7 | Muffy |  24 | Indore  | 10000.00 |
+----+-------+-----+---------+----------+
1 row in set (0.00 sec)

Numeric Expression

Numeric expressions are comprised of two operands and an SQL Arithmetic Operator. These expressions are used to perform any mathematical operation in any query. Hence, the operands must always be numerals and the return value will always be a number as well.

Syntax

Following is the syntax −

SELECT numerical_expression as OPERATION_NAME
FROM table_name
WHERE NUMERICAL EXPRESSION ;

Here, the numerical_expression is used for a mathematical expression or any formula.

Example

Following is a simple example showing the usage of SQL Numeric Expressions −

SELECT (15 + 6) AS ADDITION

Output

The output table is retrieved as −

+----------+
| ADDITION |
+----------+
|       21 |
+----------+
1 row in set (0.00 sec)

Example

There are several built-in functions pke avg(), sum(), count(), etc., to perform what is known as the aggregate data calculations against a table or a specific table column.

SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS;

Output

The output is displayed as follows −

+---------+
| RECORDS |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

Date Expressions

Date Expressions are used to compare date related values with current system date and time values.

For instance, in a manufacturing company, items manufactured per year can be segregated by using date expressions in a WHERE clause. Counting from the first day of an year to the last day, the count of each item will be retrieved; once the required information is gathered, the company can use this information for their own purposes.

Syntax

Following is the syntax −

SELECT column_name(s)
FROM table_name
WHERE DATE EXPRESSION ;

Example

In this example we are trying to simply retrieve the current timestamp of the system using CURRENT_TIMESTAMP.

SELECT CURRENT_TIMESTAMP;

Output

The output table is displayed as −

+---------------------+
| Current_Timestamp   |
+---------------------+
| 2009-11-12 06:40:23 |
+---------------------+
1 row in set (0.00 sec)

Example

Following example is using WHERE clause to retrieve the Date values less than 1st June, 2008 from our ORDERS table shown below.

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

SELECT DATE FROM ORDERS WHERE DATE <  2008/06/01 ;

Output

The output table is displayed as −

+-------------------------+
| DATE                    |
+-------------------------+
| 2008-05-20 00:00:00     |
+-------------------------+
1 row in set (0.00 sec)