DATA with BARAA

SQL AND, OR, NOT

In this tutorial, you will learn how to combine multiple conditions in the WHERE clause using the logical operators AND and OR.

In the previous tutorial, you learned how to filter your data using one condition, but sometimes you need to filter rows based on multiple conditions. The logical operators allow you to use multiple comparisons in one query.

AND Return True if both conditions are True
OR Return True if one of conditions is True
NOT Reverse the result of any Boolean operator
IN Return True if a value is in a set of values
BETWEEN Return True if a value falls within a specific range
LIKE Return True if a value matches a pattern

These logical conditions always return true or false.

The  AND  operator combines two conditions and returns TRUE only if both conditions are met and if one of the criteria is not met then it returns FALSE.

Syntax

The basic syntax of the AND clause to filter the data returned by a query can be given with:

				
					SELECT
    column_names
FROM table_name
WHERE condition1 AND condition2
				
			

The basic syntax of the OR clause to filter the data returned by a query can be given with:

				
					SELECT column_names
FROM table_name
WHERE condition1 OR condition2
				
			

The basic syntax of the NOT clause to filter the data returned by a query can be given with:

				
					SELECT column_names
FROM table_name
WHERE NOT condition
				
			
.
.

AND Example

To understand the ORDER BY statement in a better way, let’s look at the following customers table in our tutorial database:

Customers
customer_id first_name last_name country score
1 Maria Cramer Germany 350
2 John Steel USA 900
3 Georg Pipps UK 750
4 Martin Müller Germany 500
5 Peter Franken USA NULL

Now, let’s check out some examples that demonstrate how it actually works.

We have the following task be to solve using SQL statements

Find all customers who come from Germany AND whose score is less than 400

The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.

You can have the same the result set by skipping the ASC, because it is the default option in ORDER BY.

				
					SELECT *
FROM customers
WHERE country = 'Germany' 
  AND score < 400
				
			

After executing the above query, you’ll get the result set something like this:

Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.

OR Example

As you can see the output contains everything the whole customers tables including all rows and columns.

Find all customers who come from Germany OR whose score is less than 400
				
					SELECT *
FROM customers
WHERE country = 'Germany' 
OR score < 400
				
			

NOT Example

You can filter your results in a number of ways using comparison and logical operators, which you’ll learn about in the next tutorials. I summarized in the following table the most important ones.

Find all customers whose score is not less than 400

You can filter your results in a number of ways using comparison and logical operators, which you’ll learn about in the next tutorials. I summarized in the following table the most important ones.

				
					SELECT *
FROM customers
WHERE NOT score < 400
				
			
.
.
Share it !