Operators#

Operators are logical, mathematical, and equality symbols used in SQL to evaluate, compare, or calculate values.

Arithmetic Operators#

Arithmetic operators perform mathematical operations on two expressions of one or more of the data types of the numeric data type category.

Operator	Meaning
+ (Add)	Addition
- (Subtract)	Subtraction
* (Multiply)	Multiplication
/ (Divide)	Division
% (Modulo)	Returns the integer remainder of a division. For example, 12 % 5 = 2 because the remainder of 12 divided by 5 is 2.

The plus (+) and minus (-) operators can also be used to perform arithmetic operations on datetime and smalldatetime values.

Logical Operators#

Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

Operator	Meaning
AND	TRUE if both Boolean expressions are TRUE.
BETWEEN	TRUE if the operand is within a range.
EXISTS	TRUE if a subquery contains any rows.
IN	TRUE if the operand is equal to one of a list of expressions.
LIKE	TRUE if the operand matches a pattern.
NOT	Reverses the value of any other Boolean operator.
OR	TRUE if either Boolean expression is TRUE.

Assignment Operator#

The equal sign (=) is the only Transact-SQL assignment operator. In the following example, the @MyCounter variable is created, and then the assignment operator sets @MyCounter to a value returned by an expression.

DECLARE @MyCounter INT;
    SET @MyCounter = 1;

The assignment operator can also be used to establish the relationship between a column heading and the expression that defines the values for the column. The following example displays the column headings FirstColumnHeading and SecondColumnHeading. The string xyz is displayed in the FirstColumnHeading column heading for all rows. Then, each product ID from the Product table is listed in the SecondColumnHeading column heading.

SELECT FirstColumnHeading = 'xyz',
       SecondColumnHeading = ProductID
  FROM Production.Product;

Bitwise Operators#

Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category. Bitwise operators convert two integer values to binary bits, perform the AND, OR, or NOT operation on each bit, producing a result. Then converts the result to an integer.

Operator	Meaning
& (AND)	Performs a bitwise logical AND operation between two integer values.
\ (OR)	Performs a bitwise logical OR operation between two specified integer values as translated to binary expressions.
^ (XOR)	Performs a bitwise exclusive OR operation between two integer values.
~ (NOT)	Performs a bitwise logical NOT operation on an integer value.

Set Operators#

Querona provides the following set operators. Set operators combine results from two or more queries into a single result set.

EXCEPT and INTERSECT#

Returns distinct rows by comparing the results of two queries.

EXCEPT returns distinct rows from the left input query that aren’t outputted by the right input query.

INTERSECT returns distinct rows that are output by both the left and right input queries operator.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

The number and the order of the columns must be the same for all queries.
The data types must be compatible.

Syntax#

{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Arguments#

<query_specification> | ( <query_expression> ) Is a query specification or query expression that returns data to be compared with the data from another query specification or query expression. The definitions of the columns that are part of an EXCEPT or INTERSECT operation do not have to be the same, but they must be comparable through implicit conversion. When data types differ, the type that is used to perform the comparison and return results is determined based on the rules for data type precedence.

When the types are the same but differ in precision, scale, or length, the result is determined based on the same rules for combining expressions.

EXCEPT Returns any distinct values from the query to the left of the EXCEPT operand that is not also returned from the right query.

INTERSECT Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

Examples#

The following query returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

   SELECT ProductID
     FROM Production.Product
INTERSECT
   SELECT ProductID
     FROM Production.WorkOrder ;

The following query returns any distinct values from the query to the left of the EXCEPT operand, that are not also found in the right query.

SELECT ProductID
  FROM Production.Product
EXCEPT
SELECT ProductID
  FROM Production.WorkOrder ;

UNION#

Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

The following are basic rules for combining the result sets of two queries by using UNION:

The number and the order of the columns must be the same for all queries.
The data types must be compatible.

Syntax#

{ <query_specification> | ( <query_expression> ) }
  UNION [ ALL ]
  <query_specification | ( <query_expression> )
 [ UNION [ ALL ] <query_specification> | ( <query_expression> )
    [ ...n ] ]

Arguments#

<query_specification> | ( <query_expression> ) Is a query specification or query expression that returns data to be combined with the data from another query specification or query expression. The definitions of the columns that are part of a UNION operation do not have to be the same, but they must be compatible through implicit conversion. When data types differ, the resulting data type is determined based on the rules for data type precedence. When the types are the same but differ in precision, scale, or length, the result is determined based on the same rules for combining expressions.

UNION Specifies that multiple result sets are to be combined and returned as a single result set.

ALL Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.

Examples#

SELECT ProductModelID, Name
  FROM Production.ProductModel
 WHERE ProductModelID NOT IN (3, 4)
 UNION
SELECT ProductModelID, Name
  FROM dbo.Gloves
 ORDER BY Name;

Comparison Operators#

Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext. The following table lists the Transact-SQL comparison operators.

Operator	Meaning
= (Equals)	Equal to
> (Greater Than)	Greater than
< (Less Than)	Less than
>= (Greater Than or Equal To)	Greater than or equal to
<= (Less Than or Equal To)	Less than or equal to
<> (Not Equal To)	Not equal to

String Concatenation Operator#

Querona provides the following string operators. String concatenation operators can combine two or more character or binary strings, columns, or a combination of strings and column names into one expression. Wildcard string operators can match one or more characters in a string comparison operation such as LIKE or PATINDEX.

+ (String Concatenation)#

An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator). For example SELECT ‘book’+’case’; returns bookcase.

+= (String Concatenation w/ assignment)#

Not supported.

Percent character (Wildcard - Character(s) to Match)#

Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix.

This specific feature works only when a source database supports such a wildcard.

Examples#

SELECT FirstName, LastName
  FROM Person.Person
 WHERE FirstName LIKE 'Dan%';

[ ] (Wildcard - Character(s) to Match)#

Matches any single character within the specified range or set that is specified between the brackets. These wildcard characters can be used in string comparisons that involve pattern matching, such as LIKE and PATINDEX.

This specific feature works only when a source database supports such a wildcard.

Examples#

SELECT a.PostalCode
  FROM Person.Address AS a
 WHERE a.PostalCode LIKE '[0-9][0-9][0-9][0-9]';

[^] (Wildcard - Character(s) Not to Match)#

Matches any single character that is not within the range or set specified between the square brackets.

This specific feature works only when a source database supports such a wildcard.

Examples#

SELECT FirstName, LastName
  FROM Person.Person
 WHERE FirstName LIKE 'Al[^a]%'
 ORDER BY FirstName;

_ (Wildcard - Match One Character)#

Matches any single character in a string comparison operation that involves pattern matching, such as LIKE and PATINDEX.

This specific feature works only when a source database supports such a wildcard.

Examples#

SELECT FirstName, LastName
  FROM Person.Person
 WHERE FirstName LIKE '_an'
 ORDER BY FirstName

Compound Operators#

Not supported.

Unary Operators#

Unary operators perform an operation on only one expression of any one of the data types of the numeric data type category.

Operator	Meaning
+ (Positive)	Numeric value is positive.
- (Negative)	Numeric value is negative.
~ (Bitwise NOT)	Returns the ones complement of the number.

The + (Positive) and - (Negative) operators can be used on any expression of any one of the data types of the numeric data type category. The ~ (Bitwise NOT) operator can be used only on expressions of any one of the data types of the integer data type category.

Relational operators#

A relational operator is a syntax element that can accept one or more named or unnamed input parameters and returns a result set. Relational operators are used as table source in a DML statement.

OPENQUERY#

Executes the specified pass-through query on the specified linked server (connection). OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. Although the query may return multiple result sets, OPENQUERY returns only the first one.

To obtain the list of linked servers (connections) available to you, use Web GUI or sp_linkedservers stored procedure.

Syntax#

OPENQUERY ( linked_server, 'query' )

Arguments#

linked_server

Is an identifier representing the name of the linked server (connection).

‘ query ‘

Is the query string executed in the linked server. The maximum length of the string is 8 KB.

Remarks#

OPENQUERY does not accept variables for its arguments.

Permissions#

Any user can execute OPENQUERY. The permissions that are used to connect to the remote server are obtained from the settings defined for the linked server (connection).

Example#

Executing a SELECT pass-through query.

SELECT *
  FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

Operators

Contents

Operators#

Arithmetic Operators#

Logical Operators#

Assignment Operator#

Bitwise Operators#

Set Operators#

EXCEPT and INTERSECT#

Syntax#

Arguments#

Examples#

UNION#

Syntax#

Arguments#

Examples#

Comparison Operators#

String Concatenation Operator#

+ (String Concatenation)#

+= (String Concatenation w/ assignment)#

Percent character (Wildcard - Character(s) to Match)#

Examples#

[ ] (Wildcard - Character(s) to Match)#

Examples#

[^] (Wildcard - Character(s) Not to Match)#

Examples#

_ (Wildcard - Match One Character)#

Examples#

Compound Operators#

Unary Operators#

Relational operators#

OPENQUERY#

Syntax#

Arguments#

Remarks#

Permissions#

Example#