|
Operator |
Description |
|
!= |
Tests two expressions not being equal to each other. |
|
!> |
Tests that the left condition is not greater than the expression to the right. |
|
!< |
Tests that the right condition is not greater than the expression to the right. |
|
< |
Tests the left condition as less than the right condition. |
|
<= |
Tests the left condition as less than or equal to the right condition. |
|
<> |
Tests two expressions not being equal to each other. |
|
= |
Tests equality between two expressions. |
|
> |
Tests the left condition being greater than the expression to the right. |
|
>= |
Tests the left condition being greater than or equal to the expression to the right. |
|
ALL |
When used with a comparison operator and subquery, if all retrieved values satisfy the search condition, the rows will be retrieved. |
|
ANY |
When used with a comparison operator and subquery, if any retrieved values satisfy the search condition, the rows will be retrieved. |
|
BETWEEN |
Designates an inclusive range of values. Used with the AND clause between the beginning and ending values. |
|
CONTAINS |
Does a fuzzy search for words and phrases. |
|
ESCAPE |
Takes the character used prior to a wildcard character to designate that the literal value of the wildcard character should be searched, rather than use the character as a wildcard. |
|
EXISTS |
When used with a subquery, EXISTS tests for the existence of rows in the subquery. |
|
FREETEXT |
Searches character-based data for words using meaning, rather than literal values. |
|
IN |
Provides an inclusive list of values for the search condition. |
|
IS NOT NULL |
Evaluates if the value is NOT null. |
|
IS NULL |
Evaluates whether the value is null. |
|
LIKE |
Tests character string for pattern matching |
|
NOT BETWEEN |
Specifies a range of values NOT to include. Used with the AND clause between the beginning and ending values. |
|
NOT IN |
Provides a list of values for which NOT to return rows for. |
|
NOT LIKE |
Tests character string, excluding those with pattern matches. |
|
SOME |
When used with a comparison operator and subquery, if any retrieved values satisfy the search condition, the rows will be retrieved. |
-- Using BETWEEN for Date Range Searches
SELECT SalesOrderID, ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '7/28/2002' AND '7/29/2002'
--Using Comparisons
SELECT ProductID,Name,StandardCost
FROM Production.Product
WHERE StandardCost < 110.0000
--Checking for NULL Values
SELECT ProductID,Name,Weight
FROM Production.Product
WHERE Weight IS NULL
--Returning Rows Based on a List of Values
SELECT ProductID,Name,Color
FROM Production.Product
WHERE Color IN ('Silver', 'Black', 'Red')
-- Wildcard Usage
-- % Represents a string of zero or more characters.
-- _ Represents a single character.
-- [] Specifies a single character, from a selected range or list.
-- [^] Specifies a single character not within the specified range.
SELECT ProductID,Name
FROM Production.Product
WHERE Name LIKE 'B%'
SELECT ProductID, Name
FROM Production.Product
WHERE Name LIKE '%/_%' ESCAPE '/'
--Using the ORDER BY Clause
SELECT p.Name,h.EndDate,h.ListPrice
FROM Production.Product p
INNER JOIN
Production.ProductListPriceHistory h
ON
p.ProductID = h.ProductID
ORDER BY p.Name, h.EndDate
SELECT p.Name,h.EndDate,h.ListPrice
FROM Production.Product p
INNER JOIN
Production.ProductListPriceHistory h
ON
p.ProductID = h.ProductID
ORDER BY p.Name DESC, h.EndDate DESC
--Using the TOP Keyword with Ordered Results
SELECT TOP 10 v.Name, v.CreditRating
FROM Purchasing.Vendor v
ORDER BY v.CreditRating DESC, v.Name
--Using the GROUP BY Clause
-- This error is raised because any column that is not used in an aggregate function in the SELECT
-- list must be listed in the GROUP BY clause.
SELECT OrderDate, SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY OrderDate
--Using GROUP BY ALL
-- In the results returned by the GROUP BY ALL example, notice that
-- TotalDueByOrderDate was NULL for those order dates not included
-- in the WHERE clause. This does not mean they have zero rows, but
-- instead, that data is not displayed for them.
SELECT OrderDate,
SUM(TotalDue) TotalDueByOrderDate
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '7/1/2001' AND '7/31/2001'
GROUP BY ALL OrderDate
--Selectively Querying Grouped Data Using HAVING
-- The HAVING clause is used after the GROUP BY clause.
-- The WHERE clause, in contrast, is used to qualify
-- the rows that are returned before the data is aggregated
-- or grouped. HAVING qualifies the aggregated
-- data after the data has been grouped or aggregated.
SELECT s.Name,
COUNT(w.WorkOrderID) Cnt
FROM Production.ScrapReason s
INNER JOIN
Production.WorkOrder w
ON
s.ScrapReasonID = w.ScrapReasonID
GROUP BY s.Name
HAVING COUNT(*)>50
--Using DISTINCT to Remove Duplicate Values
SELECT DISTINCT HireDate
FROM HumanResources.Employee
--Using DISTINCT in Aggregate Functions
SELECT AVG(DISTINCT ListPrice)
FROM Production.Product
--Using Column Aliases
SELECT Color AS 'Grouped Color',
AVG(DISTINCT ListPrice) AS 'Average Distinct List Price',
AVG(ListPrice) 'Average List Price'
FROM Production.Product
GROUP BY Color
--Performing String Concatenation
SELECT 'The ' +
p.Name +
' is only ' +
CONVERT(varchar(25),p.ListPrice) +'!'
FROM Production.Product p
WHERE p.ListPrice between 100 AND 120
ORDER BY p.ListPrice
--Creating a Comma Delimited List Using SELECT
DECLARE @Shifts varchar(20)
SET @Shifts = ''
SELECT @Shifts = @Shifts + s.Name + ','
FROM HumanResources.Shift s
ORDER BY s.EndTime
SELECT @Shifts
--Using the INTO Clause
SELECT CustomerID,
Name,
SalesPersonID,
Demographics
INTO Store_Archive
FROM Sales.Store
--Using Subqueries to Check for the Existence of Matches
SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
WHERE EXISTS ( SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000 AND
SalesOrderID = s.SalesOrderID)
SELECT SalesPersonID,
SalesQuota CurrentSalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota
IN
(SELECT MAX(SalesQuota)
FROM Sales.SalesPerson)
--Using INNER Joins
SELECT p.Name,
s.DiscountPct
FROM Sales.SpecialOffer s
INNER JOIN Sales.SpecialOfferProduct o
ON s.SpecialOfferID = o.SpecialOfferID
INNER JOIN Production.Product p
ON o.ProductID = p.ProductID
WHERE p.Name = 'All-Purpose Bike Stand'
--Using OUTER Joins
SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
INNER JOIN Sales.SalesTaxRate t
ON s.StateProvinceID = t.StateProvinceID
SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
LEFT OUTER JOIN Sales.SalesTaxRate t
ON s.StateProvinceID = t.StateProvinceID
--Using CROSS Joins: S.Num * T.Num
SELECT s.CountryRegionCode,
s.StateProvinceCode,
t.TaxType,
t.TaxRate
FROM Person.StateProvince s
CROSS JOIN Sales.SalesTaxRate t
--Performing Self-Joins
SELECT e.EmployeeID,
e.Title,
m.Title AS ManagerTitle
FROM HumanResources.Employee e
LEFT OUTER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID
--Using Derived Tables
SELECT DISTINCT s.PurchaseOrderNumber
FROM Sales.SalesOrderHeader s
INNER JOIN (SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE UnitPrice BETWEEN 1000 AND 2000) d
ON
s.SalesOrderID = d.SalesOrderID
--Combining Result Sets with UNION
-- The UNION operator is used to append the results of two or more SELECT statements into a single
-- result set. Each SELECT statement being merged must have the same number of columns, with the
-- same or compatible data types in the same order, as this example demonstrates:
SELECT SalesPersonID, GETDATE() QuotaDate, SalesQuota
FROM Sales.SalesPerson
WHERE SalesQuota > 0
UNION
SELECT SalesPersonID, QuotaDate, SalesQuota
FROM Sales.SalesPersonQuotaHistory
WHERE SalesQuota > 0
ORDER BY SalesPersonID DESC, QuotaDate DESC