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.

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.

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.

Set Operators

Lyftron 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.

String Concatenation Operator

Lyftron 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%'; 
GO

[ ] (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]';
GO

[^] (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.

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.