Operators in t-sql

Operators have the following precedence levels, where 1 is the highest level and 6 is the lowest:

1. unary (single argument) - + ~
2. * /%
3. binary (two argument) + - & | ^
4. not
5. and
6. or
    

Bitwise operators
So we have bitwise operators like we have in c .. these are & , | , ^ and ~ operators.

The string operator + can concatenate two or more character or binary expressions. For example:

1. select Name = (au_lname + ",
   " + au_fname) 
   from authors

   Displays author names under the column heading "Name" in last-name, first-name order, with a comma after the last name; for example, "Bennett, Abraham."

2. select "abc" + "" + "def" 

   Returns the string "abc def". The empty string is interpreted as a single space in all char, varchar, nchar, nvarchar, and text concatenation, and in varchar insert and assignment statements.

When concatenating non-character, non-binary expressions, use convert:

select "The date is " + 
    convert(varchar(12), getdate()) 

The comparison operators
Adaptive Server uses these comparison operators:

Comparison operators

Operator	Meaning
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to
!=	Not equal to (Transact-SQL extension)
!>	Not greater than (Transact-SQL extension)
!<	Not less than (Transact-SQL extension)

In comparing character data, < means closer to the beginning of the server's sort order and > means closer to the end of the sort order. Uppercase and lowercase letters are equal in a sort order that is not case sensitive. Use sp_helpsort to see the sort order for your Adaptive Server. Trailing blanks are ignored for comparison purposes.
In comparing dates, < means earlier than and > means later than.
Put single or double quotes around all character and datetime data used with a comparison operator:

= "Bennet" 
> "May 22 1947"

Nonstandard operatorsThe following operators are Transact-SQL extensions:

• Modulo operator: %
• Negative comparison operators: !>, !<, !=
• Bitwise operators: ~, ^, |, &
• Join operators: *= and =*

Comparing character expressionsAdaptive Server treats character constant expressions as varchar. If they are compared with non-varchar variables or column data, the datatype precedence rules are used in the comparison (that is, the datatype with lower precedence is converted to the datatype with higher precedence). If implicit datatype conversion is not supported, you must use the convert function (see "Supported conversions").
Comparison of a char expression to a varchar expression follows the datatype precedence rule; the "lower" datatype is converted to the "higher" datatype. All varchar expressions are converted to char (that is, trailing blanks are appended) for the comparison.
Using the empty stringThe empty string ("") or ('') is interpreted as a single blank in insert or assignment statements on varchar data. When varchar, char, nchar, or nvarchar data is concatenated, the empty string is interpreted as a single space. For example, this is stored as "abc def":

"abc" + "" + "def"

The empty string is never evaluated as NULL.
Including quotation marks in character expressionsThere are two ways to specify literal quotes within a char or varchar entry. The first method is to use an additional quote with a quote of the same type. This is called "escaping" the quote. For example, if you begin a character entry with a single quote, but you want to include a single quote as part of the entry, use two single quotes:

'I don't understand.' 

Here is an example containing internal double and single quotes. The single quote does not have to be escaped, but the double quote does:

"He said, ""It's not really confusing.""" 

The second method is to enclose a quote in the opposite kind of quotation mark. In other words, surround an entry containing a double quote with single quotes (or vice versa). Here are some examples:

'George said, "There must be a better way."' 
"Isn't there a better way?" 
'George asked, "Isn"t there a better way?"' 

To continue a character string that would go off the end of one line on your screen, enter a backslash (\) before going to the following line.
If the quoted_identifier option is set to on, do not use double quotes around character or date data. You must use single quotes, or Adaptive Server treats the data as an identifier. For more information about quoted identifiers, see "Delimited identifiers".

Relational and logical expressions

A logical expression or relational expression returns TRUE, FALSE, or UNKNOWN. The general patterns are:

expression comparison_operator [any | all] expression

expression [not] in expression

[not] exists expression

expression [not] between expression and expression

expression [not] like "match_string" [escape "escape_character"]

not expression like "match_string" [escape "escape_character"]

expression is [not] null

not logical_expression

logical_expression {and | or} logical_expression

Using any, all, and inany is used with <, >, or = and a subquery. It returns results when any value retrieved in the subquery matches the value in the where or having clause of the outer statement. all is used with < or > and a subquery. It returns results when all values retrieved in the subquery are less than (<) or greater than (>) the value in the where or having clause of the outer statement. See Chapter 5, "Subqueries: Using Queries Within Other Queries," for more information.
in returns results when any value returned by the second expression matches the value in the first expression. The second expression must be a subquery or a list of values enclosed in parentheses. in is equivalent to = any.
Connecting expressions with and and orand connects two expressions and returns results when both are true. or connects two or more conditions and returns results when either of the conditions is true.
When more than one logical operator is used in a statement, and is evaluated before or. Use parentheses to change the order of execution.
Table 1-6 shows the results of logical operations, including those that involve null values:

Truth tables for logical expressions

and	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
NULL	UNKNOWN	FALSE	UNKNOWN
or	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
NULL	TRUE	UNKNOWN	UNKNOWN
not
TRUE	FALSE
FALSE	TRUE
NULL	UNKNOWN

The result UNKNOWN indicates that one or more of the expressions evaluates to NULL, and that the result of the operation cannot be determined to be either TRUE or FALSE.