{"id":312,"date":"2016-03-29T04:37:14","date_gmt":"2016-03-29T04:37:14","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=312"},"modified":"2025-02-07T20:34:37","modified_gmt":"2025-02-08T03:34:37","slug":"sql-and","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-and\/","title":{"rendered":"SQL AND Operator"},"content":{"rendered":"\n

Summary<\/strong>: This tutorial introduces you to the SQL AND<\/code> operator and shows you how to apply it to form flexible conditions in the WHERE<\/code> clause of a query.<\/p>\n\n\n\n

Introduction to SQL AND operator #<\/a><\/h2>\n\n\n\n

The AND<\/code> operator is a logical operator<\/a> that combines two Boolean expressions in the WHERE<\/a><\/code> clause of the SELECT<\/a><\/code>, UPDATE<\/a><\/code>, or DELETE<\/a><\/code> statement.<\/p>\n\n\n\n

Here’s the syntax of the AND<\/code> operator:<\/p>\n\n\n

expression1 AND expression2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The AND<\/code> operator returns true<\/code> if both expressions are evaluated to true<\/code>. If one of the two expressions is false or null, then the AND<\/code> operator returns false even if one of the expressions is NULL.<\/p>\n\n\n\n
The following table illustrates the results of the AND<\/code> operator when combining true, false, and NULL values using the AND<\/code> operator:<\/p>\n\n\n\n
\n\n\n\n\n\n\n\n\n\n\n
expression1<\/th>\nexpression2<\/th>\nexpression1 AND expression2<\/th>\n<\/tr>\n<\/thead>\n
true<\/td>\ntrue<\/td>\ntrue<\/td>\n<\/tr>\n
true<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n
true<\/td>\nNULL<\/td>\nNULL<\/td>\n<\/tr>\n
false<\/td>\nfalse<\/td>\nfalse<\/td>\n<\/tr>\n
false<\/td>\nNULL<\/td>\nfalse<\/td>\n<\/tr>\n
NULL<\/td>\nNULL<\/td>\nNULL<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n\n\n\n
SQL AND operator examples #<\/a><\/h2>\n\n\n\n
We will use the employees<\/code> table from the sample database<\/a> to demonstrate the AND<\/code> operator.<\/p>\n\n\n\n
\"employees_table\"<\/figure>\n\n\n\n
The following statement finds all employees who have both job id 9 and a salary greater than 5,000:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  job_id,\n  salary\nFROM<\/span>\n  employees\nWHERE<\/span>\n  job_id = 9<\/span>\n  AND<\/span> salary > 5000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name | last_name | job_id | salary\n------------+-----------+--------+---------<\/span>\n Alexander  | Hunold    |      9 | 9000.00\n Bruce      | Ernst     |      9 | 6000.00<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To find all the employees who joined the company between 1997 and 1998, you use the AND<\/code> operator as follows:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  last_name,\n  hire_date\nFROM<\/span>\n  employees\nWHERE<\/span>\n  EXTRACT<\/span>(year<\/span> from<\/span> hire_date) >= 1998<\/span>\n  AND<\/span> EXTRACT<\/span>(year<\/span> from<\/span> hire_date) <= 1999<\/span>\nORDER<\/span> BY<\/span> hire_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  |  last_name  | hire_date\n-------------+-------------+------------<\/span>\n Valli       | Pataballa   | 1998-02-05\n Jose Manuel | Urman       | 1998-03-07\n Jonathon    | Taylor      | 1998-03-24\n Jack        | Livingston  | 1998-04-23\n Irene       | Mikkilineni | 1998-09-28\n Guy         | Himuro      | 1998-11-15\n Diana       | Lorentz     | 1999-02-07\n Kimberely   | Grant<\/span>       | 1999<\/span>-05<\/span>-24<\/span>\n Karen       | Colmenares  | 1999<\/span>-08<\/span>-10<\/span>\n Luis        | Popp        | 1999<\/span>-12<\/span>-07<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Short-circuit evaluation #<\/a><\/h2>\n\n\n\n
The short-circuit feature allows the database system to stop evaluating the remaining parts of a logical expression as soon as it can determine the result.<\/p>\n\n\n\n
Let’s take a look at an example to get a better understanding of how the short-circuit evaluation feature works.<\/p>\n\n\n\n
See the following statement:<\/p>\n\n\n
SELECT<\/span>\n  1<\/span> = 0<\/span>\n  AND<\/span> 1<\/span> = 1<\/span> AS<\/span> result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n f<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The database system processes the two comparisons and uses the AND<\/code> operator to evaluate the two results.<\/p>\n\n\n\n
Due to the short-circuit evaluation feature, the database system has to evaluate the first expression only:<\/p>\n\n\n
1 = 0<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The reason is that the first expression returns false<\/code> that causes the whole condition to returns false<\/code> regardless of the result of the second expression.<\/p>\n\n\n\n
The short-circuit feature decreases the CPU computation and, in some cases, helps prevent runtime errors. For example:<\/p>\n\n\n
SELECT<\/span>\n  1<\/span> = 0<\/span>\n  AND<\/span> 1<\/span> \/ 0<\/span> = 1<\/span> AS<\/span> result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n f<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the database system supports the short-circuit feature, it will not evaluate the right part of the expression (1\/0) that causes the division by zero error.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n