by krlozanov on April 21st, 2012 at 14:50
Posted In: sql tutorial

SQL AND & OR

As we mention in WHERE chapter, WHERE clause accept multiple conditions. Here are two important topics about AND & OR operators:

  • There is no limit to the number of conditions in one query.
  • You can use both conditions in one query.

SQL AND & OR syntax

Syntax is really simple. First we have one condition which is presented with WHERE clause, and than we list all necesery conditions with AND/OR.

1
2
3
4
5

...
WHERE 'condition1'='value' 
AND/OR 'conndition2'='value'
AND/OR 'conndition3'='value';
...

SQL AND operator

SQL AND operator is used to affect only results which contains all conditions. This is useful when you have table with many rows and you want to view/modify/delete etc. exact one or more of them. Following examples will help you to understand this operator.

SQL AND examples

Well now imagine that you have a table with name “fruits”. Your table has 5 columns – id, fruit, price, quality, amount. You already have some records:

Output:

idfruit price quality amount
’1′‘apple’’2′‘perfect’’10′
’2′‘melon’’3′‘bad’’4′
’3′‘orange’’4′‘normal’’8′
’4′‘cherry’’3′‘perfect’’8′
’5′‘apricot’’6′‘perfect’’2′
’6′‘strawberry’’9′‘normal’’9′
’7′‘plum’’8′‘bad’’5′
’8′‘kiwi’’5′‘perfect’’2′
’9′‘banana’’7′‘normal’’7′
’10′‘blackberry’’3′‘perfect’’4′

Now our first example is to view only records which have “perfect” in quality column and more than “3″ in amount column. Following lines will present how to select this:

1
2
3
4

SELECT * 
FROM fruits 
WHERE quality='perfect' 
AND amount>'3';

And the output is:

Output:

idfruit price quality amount
’4′‘cherry’’3′‘perfect’’8′
’10′‘blackberry’’3′‘perfect’’4′

SQL OR operator

OR operator is used to affect results which contains one or another condition.

SQL AND examples

Now we will continue using above example in which we have table with name “fruits” and 5 columns – id, fruit, price, quality, amount. Now we want to view all rows in which price is lower than “3″ or quality is “bad”.

1
2
3
4

SELECT * 
FROM fruits 
WHERE price<'3' 
OR quality='bad';

First row in result matches first condition (price lower than “3″) and other two rows matches second condition (“bad” quality).

Output:

idfruit price quality amount
’1′‘apple’’2′‘perfect’’10′
’2′‘melon’’3′‘bad’’4′
’7′‘plum’’8′‘bad’’5′

Multiple operators

Our last example is focused in multiple operators. Please check the following query:

1
2
3
4
5
6

SELECT * 
FROM fruits 
WHERE price>'3' 
AND price<'8'
AND quality='perfect'
OR fruit='banana';

Output:

idfruit price quality amount
’5′‘apricot’’6′‘perfect’’2′
’8′‘kiwi’’5′‘perfect’’2′
’9′‘banana’’7′‘normal’’7′

So in above example in one side we select all rows which are between 3 and 8 for column price and value perfect in quality column, in other side all rows which have value “banana” in fruit column.




└ Tags: , ,