Tuesday 28 August 2012

SELECT with conditions

To control which rows of data to retrieve, we can apply various conditions for the table values and only display rows that match that condition

 

Format


SELECT <attributes> FROM <source table> WHERE <conditio>'

 

Basic condition <> symbol means "not equal"

SELECT*FROM table WHERE attribute = "value";
SELECT*FROM table WHERE attribute <> "value";
SELECT*FROM table WHERE attribute >1;
SELECT*FROM table WHERE attribute >=1;
SELECT*FROM table WHERE attribute <=1;

Condition for NULL values

SELECT*FROM table WHERE attribute IS NOT NULL;
SELECT*FROM table WHERE attribute IS NULL;


Multiple condition in one statement

SELECT*FROM table WHERE att1="a" AND att2="b";
SELECT*FROM table WHERE att1="a" OR att1="b";

Specify a range using either the AND command or BETWEEN command

SELECT*FROM table WHERE attribute >=0 AND attribute <=100;
SELECT*FROM table WHERE attribute BETWEEN 0 AND 100;

Specify multiple values an attribute can match

SELECT * FROM table WHERE attribute IN ("a", "b", "c");

Match a sub-word in the value.*means anything can be in that position a _means any one character can be in that position. 

*Note: LIKE with_is not available in Microsoft SQL
**Note: In My SQL, use a % symbol instead of a *
SELECT*FROM table WHERE attribute LIKE "*word*';
SELECT*FROM table WHERE attribute LIKE "wo_d";

Note:  It is possible to compare date values in a condition such as
WHERE game_date > #03-19-04#

Microsoft Access SQL uses date format MM/DD/YY wrapped with #.
Fro example if the date was March 19th 2004 the the SQL value should be #03-19-04#.

My SQL uses " " rather than # # for dates and in the format YYYY/MM/DD.
For example "2004-03-19".





No comments:

Post a Comment