| Keywords | Meaning | Example |
| > | greater than | |
| < | less than | |
| = | equals | |
| <= | less than or equal to | |
| >= | greater than or equal to | |
| != or <> | not equal | |
| !< | not less than | |
| Between AA AND BB; | not less than |
WHERE Orderdate BETWEEN '2001-07-02' AND '2005-04-03' |
| NOT BETWEEN D AND E; | --out side of range | |
| Like | Matching, can work with %,_ or []
% : replaces any number of character _ : replaces one character []:replace one character based on the values within the brackets |
SELECT Columns
FROM Table_name
WHERE LastName LIKE 'San%'
OR LastName LIKE 'Bec_'
OR LastName LIKE 'PE[T,t,s]
OR LastName LIKE 'B[a-e]'
OR LastName LIKE 'Bec[^d]%'
OR LastName LIKE 'Ber[r,g]%'
OR LastName LIKE 'Be%n_%'
|
| IN | Compare Multiple value |
SELECT Columns
FROM Table_name
WHERE FirstName = 'Ken' AND LastName IN ('Myer', 'Meyer') AND TerritoryID IN (1,2,3,4,5) AND TerritoryID NOT IN (0,6,7);
|
| IS [NOT] NULL | When a where clause meets with NULL value, it returns Unknown, not False/True. so, the results doesn't return any record. |
SELECT Columns FROM Table_name WHERE MiddleName IS NULL OR MiddleName != 'B'; |
| Contains | Full Text Index Search |
SELECT Columns FROM Table_name WHERE Contains(Document,'important') AND Contains(Document, '"service guidelines "') AND Contains(DocumentSummary, 'bicycle AND reflectors') ---AND AND Contains(DocumentSummary, 'bicycle AND NOT reflectors') ---AND NOT AND Contains(DocumentSummary, 'maintain NEAR bicycle AND NOT reflectors') ---NEAR .. AND NOT AND DocumentLevel = 2 ---Below Three get same results--- WHERE CONTAINS((DocumentSummary,Document),'maintain'); WHERE CONTAINS((DocumentSummary),'maintain') OR CONTAINS((Document),'maintain'); WHERE CONTAINS(*,'maintain'); |
| FREETEXT | FREETEXT returns similar results too. FREETEXT is less precise than CONTAINS, and it is less flexable |
SELECT Columns FROM Table_name WHERE FREETEXT((DocumentSummary),'provides'); --> returns the rows WHERE DocumentSummary LIKE '%provides%'; -->return nothing |
DB/MS SQL
