ShenoyWrites
2. MySQL: AND, OR & NOT Operators

2. MySQL: AND, OR & NOT Operators

Nikhil Shenoy's photo
Nikhil Shenoy
·

2 min read

We ended the last blog with the WHERE clause; now, we will be looking at some of the operators that can be used with the WHERE clause.

Points to remember:

  • MySQL is not case-sensitive. It means that the query select * from city will return the same output as SELECT * FROM CITY The only difference we can see would be the column names in the output will have the same format as the ones in the query.

  • Writing the keywords in Upper case is considered to be a good practice.

AND OPERATOR:

The AND operator gives records as output only when all the conditions separated by AND are satisfied.

  • The syntax is : SELECT * FROM table_name WHERE condition_1 AND condition_2;

  • Let us have a look at the content of the city table.

SELECT * FROM city;

Output:

  • Now we want the cities whose country_ids are between 15 and 50(inclusive).

  • Here we use the AND clause.

SELECT * FROM city 
WHERE country_id >= 15 AND country_id <= 50;

Output:

OR OPERATOR:

The OR operator gives records as output even if one of the conditions separated by OR is satisfied.

  • The syntax is : SELECT * FROM table_name WHERE condition_1 OR condition_2;

  • Now we want the cities whose country_ids are 10 or 20, or 44.

  • Here we use the OR clause.

SELECT * FROM city 
WHERE country_id = 10 OR country_id =20 OR country_id = 44;

Output:

NOT OPERATOR:

The NOT operator gives records as output even if the conditions are NOT satisfied.

  • The syntax is : SELECT * FROM table_name WHERE NOT condition_1;

  • Now we want the cities whose country_ids are other than 1.

  • Here we use the NOT clause.

SELECT * FROM city 
WHERE NOT country_id = 1;

Output: I have sorted the table according to the country_id( directly in the workbench). We can use the ORDER BY keyword, which will be covered in the future blog.

  • Suppose we want the cities whose country_ids are other than 1, 2, and 3.
SELECT * FROM city 
WHERE NOT (country_id = 1 OR country_id =2 OR  country_id = 3);

Output:

So here we have covered the AND, OR & NOT operators in SQL.

We'll meet again tomorrow.

SQLBeginner DevelopersOperators