SQL Operators :
Question – What is an Operator?
SQL Multiplication (*) Operator
Multiplication Operator is used to Multiply one value with the other. For example, The following query will calculate the Tax for late payments.
SQL Division (/) Operator
Division Operator is used to Divide one value with the other. For example, The following query will calculate the percentage of Tax we are paying for each individual product.
SQL Modulo (%) Operator
Modulo provides the integer remainder after dividing the first numeric expression by the second one.
Rules on Set Operations:
1.Retrieve the same number of columns and
2.The data types of corresponding columns in each involved SELECT must be compatible (either the same or with possibility implicitly convert to the data types of the first SELECT statement).
INTERSECT Operator
The INTERSECT operator takes the result of two queries and returns common rows which appears in both the result sets excluding the duplicate values. The following figure explains the same -
EXCEPT Operator:
The EXCEPT operator returns distinct rows from the first query which do not appear into the second result set. EXCEPT clause in SQL Server is working as like MINUS operation in Oracle. The following figure explains the same -
This SQL operator is used to compare the column data with specific values.
ISO Standard Operators
Non-Standard Operators:
= (Equal)
<>
It is used to check that the values of two operands are equal or not, if values are not equal then condition becomes true.
(a <> b) is true.
> (Greater than)
< (Less than)
Value of Left operand is Less than or Equal to (<=) Value of Right operand.
(a <= b) is true.
!<
It is used to check that the value of left operand is not less than the value of right operand, if yes then condition becomes true.
(a !< b) is false.
!>
It is used to check that the value of left operand is not greater than the value of right operand, if yes then condition becomes true.
(a !> b) is true.
Let’s see how Comparison operator works practically by considering some examples. We will create one table and will give name as employee in our database . After creating the mentioned table, we will insert some dummy data to look the working of Comparison SQL operator.
–Creating a table employee in the database
Select * from employee WHERE salary>20000;
4. (<) – Less than operator :-
Query to find the employee whose salary is greater than 20000.
Select * from employee WHERE salary<20000;
5. (<=) – Lessthan or Equal to operator :-
Query to find the employee whose salary is Less than or equa lto 20000*/
Select * from employee WHERE salary<=20000;
6. (>=) – Greater than or Equal to operator :-
Query to find the employee whose salary is greater than or equal to 20000*/
Select * from employee WHERE salary>=20000;
Tips and Tricks :-
String Concatenation Operator
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 matches one or more characters in a string comparison operation such as LIKE or PATINDEX.
Unary Operators:
Using + (positive) unary operator for table fields in SELECT clause
UnitPrice
18.00
Above query returns positive value only.
Question – What is an Operator?
An operator is a symbol specifying an action that is performed on one or more expressions. The following tables lists the operator categories that SQL Server uses.
Every operator is significant and performs a specific operation. The main purpose of using SQL Operators is to filter the data to be selected and are widely used in Expressions or Conditional statements of the WHERE and HAVING clause. Operators are the reserved words and used to perform operations like comparison, mathematical, etc.
We have different types of SQL operators
Arithmetical operators.
Assignment operators.
Comparison operators.
Logical operators.
SQL Addition (+) Operator
In SQL Server, Addition Operator is useful in multiple ways. If we use the addition operator on Numerical data then it will add those values and provide the integer output. If we used the Addition operator in between two strings then it will combine them and provide string output.
OUTPUT for the above statement:
NOTE: ‘-‘ is added between the product name and color
SQL Subtraction (-) Operator
Subtract Operator is used to subtract one value from the other. For example, The following query will calculate the Profit Margin by subtracting the standard cost from the dealer price.
We have different types of SQL operators
Arithmetical operators.
Assignment operators.
Comparison operators.
Logical operators.
Bit-wise Operators
Compound Operators
String concatenation operators.
Unary operators.
String concatenation operators.
Unary operators.
Scope Resolution Operators
Set Operators
Arithmetic Operators
Arithmetic Operators are used to perform Arithmetic operations such as Addition, Subtraction, Multiplication and Division on given Data.
Arithmetic Operators
Arithmetic Operators are used to perform Arithmetic operations such as Addition, Subtraction, Multiplication and Division on given Data.
Source table columns :
he following query will combine the [ProductName] and [Color] columns:
he following query will combine the [ProductName] and [Color] columns:
OUTPUT for the above statement:
NOTE: ‘-‘ is added between the product name and color
When any arithmetic operation is performed on a NULL value, the result is always NULL because NULL values have no explicitly assigned values. Arithmetic operations can be performed on more than one column at a time. Consider the following query code:
SELECT Title_id=title_id, sValue=ytd_sales*price from titles
The above query computers the product of ytd_sales and price from the titles table and displays the output with the use-defined headings.
Some rules regarding the usage of arithmetic operators are :
Arithmetic operators can be performed on numeric columns or numeric constants.
The modulo (%) operator cannot be used with columns of money, smallmoney, float or real datatypes.
SELECT Title_id=title_id, sValue=ytd_sales*price from titles
The above query computers the product of ytd_sales and price from the titles table and displays the output with the use-defined headings.
Some rules regarding the usage of arithmetic operators are :
Arithmetic operators can be performed on numeric columns or numeric constants.
The modulo (%) operator cannot be used with columns of money, smallmoney, float or real datatypes.
Subtract Operator is used to subtract one value from the other. For example, The following query will calculate the Profit Margin by subtracting the standard cost from the dealer price.
Multiplication Operator is used to Multiply one value with the other. For example, The following query will calculate the Tax for late payments.
Division Operator is used to Divide one value with the other. For example, The following query will calculate the percentage of Tax we are paying for each individual product.
Modulo provides the integer remainder after dividing the first numeric expression by the second one.
Syntax of % ( Modulo ) Operators :
dividend % divisor
dividend % divisor
Dividend is the numeric expression to divide. Dividend must be any expression of integer data type in SQL server.
Divisor is the numeric expression to divide the dividend. Divisor must be expression of integer data type except in SQL server.
The "mod" operator in computer languages is simply the remainder. For
example,
17 mod 3 = 2
because 17 / 3 = 5 rem 2
which in turn means 17 = 3 * 5 + 2
Finally, let’s see how Arithmetic SQL operator is useful in the real world by working out with some examples. Before we start with the demonstration of this SQL operator, we will declare 3 variables out of which 2 variables acts as inputs and 1 will give us the output.
Perform Arithmetical operations by using above variables.
1. (+) – For Addition :-
set @c=@a+@b
Select @c as addition
2. (-) – For Subtraction :-
set @c=@a-@b
Select @c as subtraction
3. (*) – For Multiplication :-
The "mod" operator in computer languages is simply the remainder. For
example,
17 mod 3 = 2
because 17 / 3 = 5 rem 2
which in turn means 17 = 3 * 5 + 2
Finally, let’s see how Arithmetic SQL operator is useful in the real world by working out with some examples. Before we start with the demonstration of this SQL operator, we will declare 3 variables out of which 2 variables acts as inputs and 1 will give us the output.
Declaring variables to perform Arithmetic operations.
declare @a int,@b int,@c int
set @a=40
set @b=20
Perform Arithmetical operations by using above variables.
1. (+) – For Addition :-
set @c=@a+@b
Select @c as addition
2. (-) – For Subtraction :-
set @c=@a-@b
Select @c as subtraction
3. (*) – For Multiplication :-
set @c=@a*@b
Select @c as multiplication
4. (/) – For Division :-
set @c=@a/@b
Select @c as division
Select @c as multiplication
4. (/) – For Division :-
set @c=@a/@b
Select @c as division
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.
ALL
It is used to compare a value to all values in another value set. It Returns TRUE if all of a set of comparisons are TRUE other wise returns FALSE
AND
It allows the existence of multiple conditions in an SQL statement’s WHERE clause. Performs a logical AND operation. The expression evaluates to TRUE if all conditions are TRUE.
ALL
It is used to compare a value to all values in another value set. It Returns TRUE if all of a set of comparisons are TRUE other wise returns FALSE
Example : SELECT * FROM Employee WHERE EMPLOYEEID >= ALL (SELECT EMPLOYEEID from Salary WHERE Salary>5000)
AND
It allows the existence of multiple conditions in an SQL statement’s WHERE clause. Performs a logical AND operation. The expression evaluates to TRUE if all conditions are TRUE.
Example:
SELECT * FROM Class
WHERE ( Marks > 40 AND Marks < 100)
ill-fill-alpha:100.0%'> data
type with a value of TRUE, FALSE, or UNKNOWN.
ANY
It is used to compare a value to any applicable value in the list according to the condition. ANY returns TRUE when the comparison specified is TRUE for ANY pair, otherwise, returns FALSE.
ANY
It is used to compare a value to any applicable value in the list according to the condition. ANY returns TRUE when the comparison specified is TRUE for ANY pair, otherwise, returns FALSE.
Example :
SELECT * FROM Colleges WHERE 20000 > ANY
(SELECT CollegeFess FROM Colleges)
You should notice that =ANY is equal to IN. But the opposite of it, <>ANY and NOT IN are NOT equal. Remember, the equal one for NOT IN is, <>ALL.SELECT * FROM Colleges WHERE 20000 > ANY
(SELECT CollegeFess FROM Colleges)
BETWEEN
It is used to search for values that are within a set of values, given the minimum value and the maximum value. It returns TRUE if the operand is within a range otherwise FALSE.
EXISTS
It is used to search for the presence of a row in a specified table that meets certain criteria. Specifies a sub query to test for the existence of rows.
Example :
SELECT * FROM Students WHERE EXISTS
(SELECT * FROM Students WHERE CollegeCode='B2')
IN
The IN operator is used to compare a value to a list of literal values that have been specified. This Operator returns TRUE if the operand is equal to one of a list of expressions Otherwise false.
LIKE
It is used to compare a value to similar values using wildcard operators. Determines whether a specific character string matches a specified pattern.
Example :
SELECT * FROM EnggColleges
WHERE CollegeName LIKE 'S%'
It is used to search for values that are within a set of values, given the minimum value and the maximum value. It returns TRUE if the operand is within a range otherwise FALSE.
Example :
Select * from Employee
WHERE y BETWEEN 5000 AND 15000
Select * from Employee
WHERE y BETWEEN 5000 AND 15000
EXISTS
It is used to search for the presence of a row in a specified table that meets certain criteria. Specifies a sub query to test for the existence of rows.
Example :
SELECT * FROM Students WHERE EXISTS
(SELECT * FROM Students WHERE CollegeCode='B2')
IN
The IN operator is used to compare a value to a list of literal values that have been specified. This Operator returns TRUE if the operand is equal to one of a list of expressions Otherwise false.
Example :
SELECT * FROM Students
WHERE StudentId IN(SELECT StudentId FROM CSEDEPT WHERE CSEDeptID=20)
SELECT * FROM Students
WHERE StudentId IN(SELECT StudentId FROM CSEDEPT WHERE CSEDeptID=20)
LIKE
It is used to compare a value to similar values using wildcard operators. Determines whether a specific character string matches a specified pattern.
Example :
SELECT * FROM EnggColleges
WHERE CollegeName LIKE 'S%'
Here, it will return all the records which has 's' as first letter in CollegeName
NOT
It reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. NOT operator is used To find rows that do not match a value.
OR
It is used to combine multiple conditions in an SQL statement’s WHERE clause. Performs a logical OR operation. The expression evaluates to TRUE if atleast one condition is TRUE.
SOME:
The SOME and ANY operators provide equivalent functionality. you can interchange the two keywords without affecting the results:
IS NULL
It is used to compare a value with a NULL value.
UNIQUE
It searches every row of a specified table for uniqueness (no duplicates).
Assignment OperatorNOT
It reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. NOT operator is used To find rows that do not match a value.
Example :
SELECT * FROM Colleges
WHERE CollegeCode NOT IN (50,100,150,200)
SELECT * FROM Colleges
WHERE CollegeCode NOT IN (50,100,150,200)
OR
It is used to combine multiple conditions in an SQL statement’s WHERE clause. Performs a logical OR operation. The expression evaluates to TRUE if atleast one condition is TRUE.
Example :
SELECT * FROM Class
WHERE EmployeeName LIKE 'K%' OR Marks > 40
SELECT * FROM Class
WHERE EmployeeName LIKE 'K%' OR Marks > 40
SOME:
The SOME and ANY operators provide equivalent functionality. you can interchange the two keywords without affecting the results:
SELECT
EngineerId,EngineerName,OvertimeRate
FROM Engineers
WHERE
OvertimeRate < SOME (SELECT HourlyRate FROM Engineers)
IS NULL
It is used to compare a value with a NULL value.
UNIQUE
It searches every row of a specified table for uniqueness (no duplicates).
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.
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.
Scope Resolution Operator
The scope resolution operator :: provides access to static members of a compound data type. A compound data type is one that contains multiple simple data types and methods.
Example:
The following example shows how to use the scope resolution operator to access the GetRoot() member of the hierarchyid type.
Bitwise Operators:
Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.
The operands for bitwise operators can be any of the data types of the integer or binary string data type categories (except for the image data type), with the exception that both operands cannot be any of the data types of the binary string data type category. The table shows the supported operand data types.
Set Operators
SET operators are mainly used to combine or Exclude the same type of data from two or more tables. Although more than one select statement will then be present, only one result set is returned.
Four Set Operators:
The four set operators union, union all, intersect and except allow us to serially combine two or more select statements.
±UNION - Combine two or more result sets into a single set, without duplicates.
±UNION ALL - Combine two or more result sets into a single set, including all duplicates.
±INTERSECT - Takes the data from both result sets which are in common.
±EXCEPT - Takes the data from first result set, but not the second (i.e. no matching to each other)
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;
GO
Scope Resolution Operator
The scope resolution operator :: provides access to static members of a compound data type. A compound data type is one that contains multiple simple data types and methods.
Example:
The following example shows how to use the scope resolution operator to access the GetRoot() member of the hierarchyid type.
DECLARE @hid hierarchyid;
SELECT
@hid = hierarchyid::GetRoot();
PRINT
@hid.ToString();
Here is the result set.
/
Bitwise Operators:
Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.
The operands for bitwise operators can be any of the data types of the integer or binary string data type categories (except for the image data type), with the exception that both operands cannot be any of the data types of the binary string data type category. The table shows the supported operand data types.
SET operators are mainly used to combine or Exclude the same type of data from two or more tables. Although more than one select statement will then be present, only one result set is returned.
Four Set Operators:
The four set operators union, union all, intersect and except allow us to serially combine two or more select statements.
±UNION - Combine two or more result sets into a single set, without duplicates.
±UNION ALL - Combine two or more result sets into a single set, including all duplicates.
±INTERSECT - Takes the data from both result sets which are in common.
±EXCEPT - Takes the data from first result set, but not the second (i.e. no matching to each other)
- The result sets of all queries must have the same number of columns.
- In every result set the data type of each column must match the data type of its corresponding column in the first result set.
- In order to sort the result, an ORDER BY clause should be part of the last statement.
- The records from the top query must match the positional ordering of the records from the bottom query.
- The column names or aliases must be found out by the first select statement.
1.Retrieve the same number of columns and
2.The data types of corresponding columns in each involved SELECT must be compatible (either the same or with possibility implicitly convert to the data types of the first SELECT statement).
UNION Operator:
The UNION operator combines the result of two or more queries and returns a single result set excluding the duplicate values.
UNION ALL Operator
However if you want to include the duplicate values of Name from Result set-1, as well as Result set-2, include ALL with UNION. The query is shown below
However if you want to include the duplicate values of Name from Result set-1, as well as Result set-2, include ALL with UNION. The query is shown below
The INTERSECT operator takes the result of two queries and returns common rows which appears in both the result sets excluding the duplicate values. The following figure explains the same -
The EXCEPT operator returns distinct rows from the first query which do not appear into the second result set. EXCEPT clause in SQL Server is working as like MINUS operation in Oracle. The following figure explains the same -
Comparison Operators:
These operators can be used to create a Boolean expression that compares the two values. The result of a comparison operator has the Boolean data type. This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions. This makes such expressions useful in WHERE clauses or conditional statements.
These operators can be used to create a Boolean expression that compares the two values. The result of a comparison operator has the Boolean data type. This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions. This makes such expressions useful in WHERE clauses or conditional statements.
- Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types
- When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except an equals (=) operator returns TRUE if both expressions are NULL. For example, NULL = NULL returns TRUE when SET ANSI_NULLS is OFF.
This SQL operator is used to compare the column data with specific values.
ISO Standard Operators
- The comparison operators can be grouped into ISO standardised operators and non-standard variations. There are six ISO standard operators, as follows:
- Equality (a = b). The equality operator returns true if the two compared values (a and b) are equal.
- Less Than (a < b). This operator returns true when 'a' is smaller than 'b'.
- Greater Than (a > b). This operator returns true when 'a' is larger than 'b'.
- Less Than Or Equal (a <= b). This operator returns true when 'a' is smaller than 'b' or the two values are equal.
- Greater Than Or Equal (a >= b). This operator returns true when 'a' is larger than 'b' or the two values are equal.
- Not Equal (a <> b). This operator returns true if the two compared values (a and b) are not equal.
- The second set of operators contains the non-standard variations. It is acceptable to use these operators but you should consider that statements that you create might not be supported on other ISO-compliant database management systems.
- Not Equal (a != b). This operator returns true if the two compared values (a and b) are not equal. It provides the same functionality as <>.
- Not Greater Than (a !> b). This operator returns true when 'a' is smaller than 'b' or the two values are equal. It provides the same functionality as <=.
- Not Less Than (a !< b). This operator returns true when 'a' is larger than 'b' or the two values are equal. It provides the same functionality as >=.
= (Equal)
It is used to check that the values of two operands are equal or not, if yes then condition becomes true.
Value of Left operand is Equal (=) to Value of Right operand.
(a = b) is not true.
!= Or <> (Not equal)
It is used to check that the values of two operands are equal or not, if values are not equal then condition becomes true.
Value of Left operand is Not equal (!= Or <>) to Value of Right operand.
(a != b) is true.
(a = b) is not true.
!= Or <> (Not equal)
It is used to check that the values of two operands are equal or not, if values are not equal then condition becomes true.
Value of Left operand is Not equal (!= Or <>) to Value of Right operand.
(a != b) is true.
<>
It is used to check that the values of two operands are equal or not, if values are not equal then condition becomes true.
(a <> b) is true.
> (Greater than)
It is used to check that the value of left operand is greater than the value of right operand, if yes then condition becomes true.
Value of Left operand is Greater than (>) Value of Right operand.
(a > b) is not true.
Value of Left operand is Greater than (>) Value of Right operand.
(a > b) is not true.
< (Less than)
It is used to check that the value of left operand is less than the value of right operand, if yes then condition becomes true.
Value of Left operand is Less than (<) Value of Right operand.
(a < b) is true.
>= (Greater than or Equal to)
It is used to check that the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
Value of Left operand is Less than (<) Value of Right operand.
(a < b) is true.
>= (Greater than or Equal to)
It is used to check that the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
Value of Left operand is Greater than or Equal to (>=)Value of Right operand.
(a >= b) is not true.
<= (Less than or Equal to)
It is used to check that the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
(a >= b) is not true.
<= (Less than or Equal to)
It is used to check that the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
Value of Left operand is Less than or Equal to (<=) Value of Right operand.
(a <= b) is true.
!<
It is used to check that the value of left operand is not less than the value of right operand, if yes then condition becomes true.
(a !< b) is false.
!>
It is used to check that the value of left operand is not greater than the value of right operand, if yes then condition becomes true.
(a !> b) is true.
Let’s see how Comparison operator works practically by considering some examples. We will create one table and will give name as employee in our database . After creating the mentioned table, we will insert some dummy data to look the working of Comparison SQL operator.
–Creating a table employee in the database
go
Create table employee (id int, name varchar(20), skills varchar(20), salary money, location char(10));
–Insert few rows into employee to perform Comparison operations.
Insert into employee_phpring values
(1,'chander sharma','msbi',20000,'pune'),
(2,'sai krishna','msbi',15000,'hyderabad'),
(3,'pinal dave','sql server',40000,'bangalore'),
(4,'vinod kumar','ms office',50000,'chennai'),
(5,'balmukund','sql server',40000,'bangalore'),
(6,'Avinash Reddy','msbi',10000,'hyderabad');
–Applying comparison operators on the above created table.
1. (=) – Equal to operator :-
Query to find the employee whose salary is equal to 20000.
Select * from employee WHERE salary=20000;
2. (!= or <>) – Not Equal to operator :-
Query to find the employee whose salary is Not equal to 20000.
Select * from employee WHERE salary!=20000
OR
Select * from employee WHERE salary<>20000;
3. (>) – Greater than operator :-
Query to find the employee whose salary is greater than 20000.
Create table employee (id int, name varchar(20), skills varchar(20), salary money, location char(10));
–Insert few rows into employee to perform Comparison operations.
Insert into employee_phpring values
(1,'chander sharma','msbi',20000,'pune'),
(2,'sai krishna','msbi',15000,'hyderabad'),
(3,'pinal dave','sql server',40000,'bangalore'),
(4,'vinod kumar','ms office',50000,'chennai'),
(5,'balmukund','sql server',40000,'bangalore'),
(6,'Avinash Reddy','msbi',10000,'hyderabad');
–Applying comparison operators on the above created table.
1. (=) – Equal to operator :-
Query to find the employee whose salary is equal to 20000.
Select * from employee WHERE salary=20000;
2. (!= or <>) – Not Equal to operator :-
Query to find the employee whose salary is Not equal to 20000.
Select * from employee WHERE salary!=20000
OR
Select * from employee WHERE salary<>20000;
3. (>) – Greater than operator :-
Query to find the employee whose salary is greater than 20000.
Select * from employee WHERE salary>20000;
4. (<) – Less than operator :-
Query to find the employee whose salary is greater than 20000.
Select * from employee WHERE salary<20000;
5. (<=) – Lessthan or Equal to operator :-
Query to find the employee whose salary is Less than or equa lto 20000*/
Select * from employee WHERE salary<=20000;
6. (>=) – Greater than or Equal to operator :-
Query to find the employee whose salary is greater than or equal to 20000*/
Select * from employee WHERE salary>=20000;
Tips and Tricks :-
- Operators in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’” generally prevents the query optimizer from using an index to perform a search.
- Exists is much faster than IN, when sub query results is very large.
- IN is faster than Exists when sub query returns small values.
- BETWEEN operator is much more useful than IN operator as Query optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause (which is really just another form of the OR clause).
- Always try to position the most expensive AND clause first in the WHERE clause sequencing. Oracle evaluates un-indexed equations, linked by the AND verb in a bottom-up fashion. This means that the first clause (last in the AND list) is evaluated, and if it is found true, then only second clause is tested.
- Always try to position the most expensive OR clause last in the WHERE clause sequencing. Oracle evaluates un-indexed equations, linked by the OR verb in a top-down fashion. This means that the first clause (first in the OR list) is evaluated, and if it is found false, then only second clause is tested
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 matches one or more characters in a string comparison operation such as LIKE or PATINDEX.
Concatenation of NULL Values
As we have seen, a NULL value represents a value that is undefined. When concatenating two strings, it is logical that combining a known value with NULL will therefore yield an undefined value. The usual behaviour when concatenating strings where one value is NULL is that the resultant string is also NULL. For example:
Compound Operators
SQL Server 2008 has introduced the new feature compound operator. Compound operators are available in other programming languages like C# etc. Compound operators are used when you want to apply an arithmetic operation on a variable and assign the value back into the variable.
+= Operator
As we have seen, a NULL value represents a value that is undefined. When concatenating two strings, it is logical that combining a known value with NULL will therefore yield an undefined value. The usual behaviour when concatenating strings where one value is NULL is that the resultant string is also NULL. For example:
SELECT 'Known' + NULL -- Returns NULL (usually)
Other String Concatenation Operators:
+= (String Concatenation)
% (Wildcard - Character(s) to Match)
[ ] (Wildcard - Character(s) to Match)
[^] (Wildcard - Character(s) Not to
Match)
_ (Wildcard - Match One Character)
Compound Operators
SQL Server 2008 has introduced the new feature compound operator. Compound operators are available in other programming languages like C# etc. Compound operators are used when you want to apply an arithmetic operation on a variable and assign the value back into the variable.
+= Operator
DECLARE @addvalue int = 53;
SET @addvalue += 20 ;
PRINT 'Add value :' + CAST(@addvalue AS VARCHAR);
--Result : Add value :73
DECLARE @concString VARCHAR(50) = 'Jignesh';
SET @concString += ' Trivedi' ;
PRINT 'Output :' + @concString;
--Result : Output :Jignesh Trivedi
-= Operator
DECLARE @subValue int = 99;
SET @subValue -= 2 ;
PRINT 'subtract value :' + CAST(@subValue AS VARCHAR);
--Result : subtract value :97
*= Operator
DECLARE @mulValue int = 75;
SET @mulValue *= 20 ;
PRINT 'Multiplication :' + CAST(@mulValue AS VARCHAR);
--Result : Multiplication :1500
/= Operator
DECLARE @divValue NUMERIC(8,2) = 27;
SET @divValue /= 2.5 ;
PRINT 'Division :' + CAST(@divValue AS VARCHAR);
--Result : Division :10.80
%= Operator
DECLARE @modulo int = 25;
SET @modulo %= 5 ;
PRINT 'Modulo :' + CAST(@modulo AS VARCHAR);
--Result : Modulo :1
DECLARE @mulValue int = 75;
SET @mulValue *= 20 ;
PRINT 'Multiplication :' + CAST(@mulValue AS VARCHAR);
--Result : Multiplication :1500
/= Operator
DECLARE @divValue NUMERIC(8,2) = 27;
SET @divValue /= 2.5 ;
PRINT 'Division :' + CAST(@divValue AS VARCHAR);
--Result : Division :10.80
%= Operator
DECLARE @modulo int = 25;
SET @modulo %= 5 ;
PRINT 'Modulo :' + CAST(@modulo AS VARCHAR);
--Result : Modulo :1
&= Operator
DECLARE @bitAnd int = 90;
SET @bitAnd &= 13 ;
PRINT 'Bitwise AND Operation:' + CAST(@bitAnd AS VARCHAR);
--Result : Bitwise AND Operation:8
^=Operator
DECLARE @bitExOr int = 244;
SET @bitExOr ^= 20 ;
PRINT 'Bitwise Exclusive OR Operation:' + CAST(@bitExOr AS VARCHAR);
--Result : Bitwise Exclusive OR Operation:224
|= Operator
DECLARE @bitOR int = 270;
SET @bitOR |= 25 ;
PRINT 'Bitwise OR Operation:' + CAST(@bitOR AS VARCHAR);
--Result : Bitwise OR Operation:287
DECLARE @bitAnd int = 90;
SET @bitAnd &= 13 ;
PRINT 'Bitwise AND Operation:' + CAST(@bitAnd AS VARCHAR);
--Result : Bitwise AND Operation:8
^=Operator
DECLARE @bitExOr int = 244;
SET @bitExOr ^= 20 ;
PRINT 'Bitwise Exclusive OR Operation:' + CAST(@bitExOr AS VARCHAR);
--Result : Bitwise Exclusive OR Operation:224
|= Operator
DECLARE @bitOR int = 270;
SET @bitOR |= 25 ;
PRINT 'Bitwise OR Operation:' + CAST(@bitOR AS VARCHAR);
--Result : Bitwise OR Operation:287
Unary Operators:
Unary operators perform an operation on only one expression of numeric data type. These unary operators can be used to convert the sign of a numeric value. Unary operators are +, -, ~
SELECT
+UnitPrice
FROM Products
Output UnitPrice
18.00
Above query returns positive value only.
Using - (negative) unary operator for table fields in SELECT clause.
SELECT –UnitPrice FROM
Products
Output
UnitPrice
-18.00
-19.00
Above query returns negative value only.
UnitPrice
-18.00
-19.00
Above query returns negative value only.
Range Operator's :
The range operator is used to retrieve data that can be extracted in ranges. The range operations are:
BETWEEN
NOT BETWEEN
The Syntax :
SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression2
Example :
=======
1. SELECT title FROM titles WHERE advance BETWEEN 2000 AND 5000
2. SELECT title FROM titles WHERE advance NOT BETWEEN 4000 AND 5000
List Operator's :
SELECT column_list FROM table_name WHERE expression list_operator('value_list')
Example :
=======
1. SELECT pub_name, city, state FROM publishers WHERE state IN ('CA', 'NY')
2. SELECT pub_name, city, state FROM publishers WHERE state NOT IN ('CA', 'NY')
String Operator's :
SQL Server provides a pattern-matching method for string expressions using theLIKE keyword with the wildcard characters. The LIKE keyword is used to select those rows that match the specified portion of character string. The Like keyword allows wildcard characters that can be used as a part of an expression.
Wild Card
%
Represents any string of zero or more characters(s)
-
Represents a single character
[]
Represents any single character within the specified range.
[^]
Represents any single character not within the specified range.
Example's of the LIKE Operator with wildcards.
Example
SELECT title FROM titles WHERE type LIKE 'bus%'
Returns all titles from titles table where first three characters of the column type are 'bus'
SELECT * FROM publishers WHERE country LIKE 'US_'
Returns all rows from publishers table where country name is three characters long and starts with US where the third character can be anything.
SELECT title_id, price FROM titles WHERE title_id LIKE 'P[SC]]%'
Returns all columns from the titles table where title_id starts with the character P and Contains S or C in the second position followed by any number of characters.
SELECT title_id, price FROM titles WHERE title_id LIKE 'P[^C]%'
Returns all title_id and price from the titles table where title_id starts with P and does not contain and S as the second character and the third position onwards can contain any characters.
The range operator is used to retrieve data that can be extracted in ranges. The range operations are:
BETWEEN
NOT BETWEEN
The Syntax :
SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression2
Example :
=======
1. SELECT title FROM titles WHERE advance BETWEEN 2000 AND 5000
2. SELECT title FROM titles WHERE advance NOT BETWEEN 4000 AND 5000
List Operator's :
SELECT column_list FROM table_name WHERE expression list_operator('value_list')
Example :
=======
1. SELECT pub_name, city, state FROM publishers WHERE state IN ('CA', 'NY')
2. SELECT pub_name, city, state FROM publishers WHERE state NOT IN ('CA', 'NY')
String Operator's :
SQL Server provides a pattern-matching method for string expressions using theLIKE keyword with the wildcard characters. The LIKE keyword is used to select those rows that match the specified portion of character string. The Like keyword allows wildcard characters that can be used as a part of an expression.
Wild Card
%
Represents any string of zero or more characters(s)
-
Represents a single character
[]
Represents any single character within the specified range.
[^]
Represents any single character not within the specified range.
Example's of the LIKE Operator with wildcards.
Example
SELECT title FROM titles WHERE type LIKE 'bus%'
Returns all titles from titles table where first three characters of the column type are 'bus'
SELECT * FROM publishers WHERE country LIKE 'US_'
Returns all rows from publishers table where country name is three characters long and starts with US where the third character can be anything.
SELECT title_id, price FROM titles WHERE title_id LIKE 'P[SC]]%'
Returns all columns from the titles table where title_id starts with the character P and Contains S or C in the second position followed by any number of characters.
SELECT title_id, price FROM titles WHERE title_id LIKE 'P[^C]%'
Returns all title_id and price from the titles table where title_id starts with P and does not contain and S as the second character and the third position onwards can contain any characters.
