This documentation is archived and is not being maintained.

Structure of Expressions

An expression consists of any combination of column names, literals, operators, or functions. Follow these guidelines in combining elements to form expressions:

  • Reference columns by typing their names. If your query uses more than one table and if you use a column name that is not unique, you must add the table name and a period to the column name. The following example shows the column name job_id qualified with the table name employee:
    employee.job_id
    
  • Include literal text by enclosing it in single quotation marks; no quotation marks are necessary for numbers.
    Note   In some databases, terms in single quotation marks are interpreted as literal values whereas terms in double quotation marks are interpreted as database objects such as column or table references. Therefore, the Query and View Designer can accept terms in double quotation marks, but might interpret them differently than you expect. In SQL Server, the Query and View Designer always interprets double quotation marks as database object delimiters. For details, see Query and View Designer Considerations for SQL Server Databases.
  • Use standard arithmetic operators for numbers and a concatenation operator for combining strings. For details, see Operators for Expressions.
  • Include parentheses to establish precedence of operators.
  • If you include a function, use these same guidelines for the arguments passed to the function. That is, reference columns by typing their names, enclose literal text in single quotation marks, and so on. For more information, see Functions for Expressions.
  • If you pass column names as function arguments, be sure the data type of the column is appropriate for the function argument.
  • You can include user-defined functions returning a scalar value in an expression. For more information about user-defined functions, see Working with Stored Procedures and User-Defined Functions.

The following table illustrates the use of expressions in a query.

Expression1 Result
SELECT (price * .9)
FROM products
Displays a discounted price (10% off the value in the price column).
SELECT (lname + ', ' + fname)
FROM employee
Displays the concatenated values of the last name and first name columns with a comma between them.
SELECT sales.qty, titles.price

FROM sales INNER JOIN titles ON
sales.title_id = titles.title_id
ORDER BY
(sales.qty * titles.price)

After joining two tables, sorts the result set by the total value of an order (quantity times price).
SELECT au_lname, au_fname
FROM authors
WHERE
(SUBSTRING(phone, 1, 3) = '415')
Displays authors whose area code is in the San Francisco area.
SELECT ord_num, ord_date
FROM sales
WHERE
(ord_date >=
DATEADD(day, -10, GETDATE()))
Finds all orders in the sales table that were made in the last 10 days. Today's date is returned by the GETDATE( ) function.

1   Some of the operators and functions shown here are specific to one database. For details about what operators and functions you can use, refer to the documentation for your database.

See Also

Reference

Show: