In a earlier tutorial, we realized easy methods to work with the WHERE clause in SQL, which database builders can use after they want to retrieve a gaggle of information that meet a sure standards or situation. For example, a database administrator might want to discover each occasion in a database the place a person’s title is the same as “Ronnie” – or, extra probably, not equal to “Ronnie”. As highly effective because the WHERE clause is in SQL, we are able to make it even stronger after we apply SQL’s NOT, AND, and OR operators.
This database improvement tutorial reveals db admins and programmers easy methods to use the NOT, AND, and OR operators in SQL, full with syntax, code examples, and use instances.
Earlier than we get began, nevertheless, you could want to assessment our tutorial: Tips on how to Use the SQL WHERE Clause.
What’s the AND Operator in SQL?
The AND operator in SQL is used to show a given report if each situation separated by the AND operator are TRUE. It’s used together with the WHERE clause to filter information primarily based on two or extra situations.
SQL AND Syntax
The syntax for the AND operator in SQL is as follows:
SELECT columnA, columnB
FROM table_example
WHERE conditionA and conditionB …;
SQL AND Code Instance
For our SQL AND operator code instance, let’s fake we have now a database named Nakamura Industries and inside that database is a desk named workers, which, as you’ll be able to think about, maintain data pertaining to worker information. Let’s additional posit that our desk has the next columns:
- firstName: Holds first title of worker
- lastName: Holds final title of worker
- employeeID: Holds distinctive quantity representing the workers ID quantity
Let’s say that this desk has the next data:
firstName lastName employeeID
Ronnie Payne 001
Donnie Layne 002
Ronnie Donnie 003
Tips on how to Use SQL AND to Retrieve a Document
Utilizing our pattern desk above, to retrieve a report from a database utilizing SQL AND, we’d write the next SQL question:
SELECT firstName, lastName, employeeID
FROM workers
WHERE firstName=”Ronnie” AND lastName=”Payne”;
On this instance question, we’re in search of an worker within the database whose first title is Ronnie and whose final title is Payne. If no report exists that meets each of these necessities, no information can be returned. Since we do, certainly, have a report the place each units of standards are met, we get the outcome:
Ronnie Payne 001
after we execute our question.
Querying A number of AND Standards in SQL
We are able to, in fact, question for greater than two units of standards in SQL utilizing the AND operator. In these instances, every bit of standards should be met. Right here is an instance exhibiting a question the place three situations should be met:
SELECT fiRead: rstName, lastName, employeeID
FROM workers
WHERE firstName=”Ronnie” AND lastName=”Payne” AND employeeID=007;
On this instance SQL question, we’re asking the database to return each report that has a primary title of Ronnie, a final title of Payne, and an worker ID matching 007.
Eager observers will be aware that the worker Ronnie Payne has an worker ID of 001; since his worker ID shouldn’t be 007 – and subsequently not all three situations are met, the question would return an empty outcome.
If we rewrote our SQL question to mirror the next, it might, as an alternative, return the specified outcome:
SELECT firstName, lastName, employeeID
FROM workers
WHERE firstName=”Ronnie” AND lastName=”Payne” AND employeeID=001;
Utilizing this up to date SQL question and AND operator, we’d get the next output:
Ronnie Payne 001
Learn: Greatest On-line Programs to Study SQL
What’s the SQL OR Operator?
Just like the SQL AND operator, the OR operator works together with the WHERE clause to question database tables for data the place one or extra situations are TRUE.
In contrast to with the AND operator, the place all situations should be TRUE, in situations the place OR is used, solely one situation should consider to TRUE.
SQL OR Operator Syntax
The syntax for SQL’s OR operator is as follows:
SELECT columnA, columnB
FROM table_example
WHERE conditionA OR conditionB …;
SQL OR Instance Code
Within the following instance, we are going to use the identical instance database from earlier than, and this time, create a question the place we’re looking for firstName values that equal both Ted or Larry:
SELECT firstName, lastName, employeeID
FROM workers
WHERE firstName=”Ted” OR firstName=”Larry”;
Working the above SQL question will return no outcomes, as desk workers accommodates no firstName information containing Ted or Larry.
Contemplate this SQL instance:
SELECT firstName, lastName, employeeID
FROM workers
WHERE firstName=”Ted” OR firstName=”Ronnie”;
On this instance, we set our standards to go looking the column firstName for both Ted or Ronnie. Since one of many OR operator’s standards is met, we get the outcome:
Ronnie Payne 001
Ronnie Donnie 003
Utilizing SQL OR to Question for A number of Standards
Just like the AND operator, database programmers and database directors can use the OR operator to retrieve information from a desk utilizing a number of standards. Right here is an instance of easy methods to use the OR operator with the WHERE clause to question for information primarily based on three units of standards:
SELECT firstName, lastName, employeeID
FROM workers
WHERE firstName=”Ted” OR firstName=”Ronnie” OR firstName=”Donnie”;
Right here, we’re in search of the worth Ronnie, Donnie, or Ted – if any of that standards is met, outcomes can be returned. Beneath is the output of operating this question:
Ronnie Payne 001
Donnie Layne 002
Ronnie Donnie 003
What’s the SQL NOT Operator
The SQL NOT operator is usually a little complicated and take a while to get used to utilizing correctly. As an alternative of utilizing it to seek out standards that’s TRUE, as an alternative, database programmers use it to seek out information the place a set of standards is NOT TRUE, which is technically totally different than FALSE.
As a result of there are values which can be equal to null in SQL, the NOT operator is typically used so question databases. The worth null is neither TRUE nor FALSE – often known as not 0 or 1. So should you wished to question a desk that had null values in it, you would use the NOT operator to seek out such values.
SQL NOT Operator Syntax
Beneath if the syntax for utilizing the NOT operator in SQL queries:
SELECT columnA, columnB
FROM table_example
WHERE NOT conditionA …;
SQL NOT Operator Code Instance
After all, the SQL NOT operator may also merely retrieve values from a desk that aren’t equal to TRUE as properly. Contemplate the next instance SQL question:
SELECT firstName, lastName, employeeID
FROM workers
WHERE NOT firstName=”Ronnie”;
Within the above instance, we’re particularly in search of information that don’t comprise Ronnie (or, put one other manner, the place the worth of firstName shouldn’t be equal to Ronnie). Since there are two information in firstName that comprise the worth Ronnie – or the workers Ronnie Payne and Ronnie Donnie – the question will return the outcomes that don’t comprise these values.
Right here can be the output of operating the above question:
Donnie Layne 002
SQL Operators: NOT, AND, OR Used Collectively
Lastly, it’s believable to make use of an SQL WHERE clause that used the AND, OR, and NOT in the identical assertion:
SELECT firstName, lastName, employeeID
FROM workers
WHERE firstName=”Ronnie” AND lastName=”Payne” OR lastName=”Donnie”;
Equally, you’ll be able to mix NOT with AND or OR operators too:
SELECT firstName, lastName, employeeID
FROM workers
WHERE NOT firstName=”Ronnie” AND NOT lastName=”Payne” OR lastName=”Donnie”;
