SQL AND,OR and NOT
SQL provides us with logical operators that makes our work easy . SQL logical operator helps in filtering the result. The three most commonly used logical operator are – AND , OR, NOT. SQL logical operator are used with ‘WHERE’. Let us discuss about SQL AND,OR and NOT operators.
SQL AND,OR and NOT –Logical operators
AND Syntax:
SELECT * FROM table_name WHERE condition 1 AND condition 2 AND condition 3....;
OR Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
NOT Syntax:
SELECT column1, column2, ... FROM table_name WHERE NOT conditon;
All the three syntax mentioned above uses the logical operator with the ‘ WHERE ‘ clause.
Practical approach to the Logical operator:
As discussed -SQL AND ,OR and NOT falls under the category of Logical operator and they are used with ‘WHERE’ clause.Let us look at some example and learn the concept of these logical operator.
Table name:-India
| S.NO | State | Area | Literacy rate(%) | Region |
|---|---|---|---|---|
| 1 | Kerala | 33863 | 93.91 | Southern |
| 2 | Maharashtra | 307713 | 83.78 | Western |
| 3 | Rajasthan | 342239 | 67.06 | Western |
| 4 | Odisha | 155707 | 73.45 | Eastern |
| 5 | West Bengal | 88752 | 77.08 | Eastern |
| 6 | Jharkhand | 79714 | 67.63 | Eastern |
Following Table has attributes like -S.NO, State, Area,Literacy rate(%),Region.
Use of ‘AND’ operator:
SELECT * FROM India WHERE region='Eastern' AND Literacy rate>70;
In The following example ‘AND’ opearaotr are used with the ‘WHERE’ clause . ‘AND’ operator joins ‘2’ conditions in the example. Only those values are selected with region=’Eastern’ and Literacy rate ought to be greater than ’70’.
| S.NO | State | Area | Literacy rate(%) | Region |
|---|---|---|---|---|
| 4 | Odisha | 155707 | 73.75 | Eastern |
| 5 | West Bengal | 88752 | 77.08 | Eastern |
Use of ‘OR’ operator:
SELECT * FROM India WHERE Area!=88752 OR Literacy rate!=67.63;
In the following above example Only those values are picked where neither area equals to the ‘88752’ value or L.rate is not equal to ‘67.63’. ‘Or’ operator is different from ‘AND ‘ and provides a extra option to the user.
| S.NO | State | Area | Literacy rate(%) | Region |
|---|---|---|---|---|
| 1 | Kerala | 33863 | 93.91 | Southern |
| 2 | Maharashtra | 307713 | 83.78 | Western |
| 3 | Rajasthan | 342239 | 67.06 | Western |
| 4 | Odisha | 155707 | 73.45 | Eastern |
Use of ‘NOT’ operator:
SELECT * FROM India WHERE NOT State='Jharkhand';
‘NOT’ operator doesn’t include values with state=’Jharkhand’. Neglects all the rows with state as ‘Jharkhand’.
| S.NO | State | Area | Literacy rate(%) | Region |
|---|---|---|---|---|
| 1 | Kerala | 33863 | 93.91 | Southern |
| 2 | Maharashtra | 307713 | 83.78 | Western |
| 3 | Rajasthan | 342239 | 67.06 | Western |
| 4 | Odisha | 155707 | 73.45 | Eastern |
| 5 | West Bengal | 88752 | 77.08 | Eastern |
Combination of AND,OR,and NOT:
We have had discussed each of them indivdually , But for large databases we might need to use all three of them at once. Logical operators could be combined with each other.Let us look at through it with a help of pratical example.
SELECT * FROM India WHERE NOT state='Westbengal' AND NOT state='jharkhand';
Select values that do not belong to either of the states mentioned in the example:
| S.NO | State | Area | Literacy rate(%) | Region |
|---|---|---|---|---|
| 1 | Kerala | 33863 | 93.91 | Southern |
| 2 | Maharashtra | 307713 | 83.78 | Western |
| 3 | Rajasthan | 342239 | 67.06 | Western |
| 4 | Odisha | 155707 | 73.45 | Eastern |
This is all about the AND , OR and NOT operators in SQL.