Data Manipulation Language
Once we've created tables using DDL, we use DML to add, modify, delete, and query the data. In the process of creating and exploring our database, we've had a chance to become briefly acquainted with a few of these commands already.
This is the set of SQL DML statements that we will learn to use in this section:
Adding, changing, and deleting data
|
INSERT
UPDATE
DELETE
|
Querying data
|
SELECT
|
Transaction control statements
|
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
|
Adding Records
The command for adding new records to a database table is INSERT. There are two general formats for the INSERT command:
INSERT INTO tablename VALUES (value1, value2, ...);
INSERT INTO tablename (column_name1, column_name2, ...)
VALUES (value1, value2, ...);
In the first format we do not list the columns that we will be supplying values for. The values must exactly match the type and default order of all the columns in the table. We can find this out with the DESC command.
This is the result for the CD_COLLECTION table:
SQL> DESC CD_COLLECTION
Name Null? Type
---------------------------------- -------- -----------
CD_ID NOT NULL NUMBER
ALBUM_TITLE VARCHAR2(100)
ARTIST VARCHAR2(100)
COUNTRY VARCHAR2(25)
RELEASE_DATE DATE
LABEL VARCHAR2(25)
To add a row, we need to provide values for all six columns, in order:
INSERT INTO CD_COLLECTION VALUES
(CD_ID_SEQUENCE.NEXTVAL, 'Horses', 'Patti Smith', 'USA', '1-JAN-1975', 'Arista');
If we wish to omit a value (and it is valid to do so), we can use the keyword NULL, as in this example:
INSERT INTO CD_COLLECTION VALUES
(CD_ID_SEQUENCE.NEXTVAL, 'Doolittle', 'Pixies', 'USA', '1-JAN-1989', NULL);
You may be wondering whether it is true that all the albums we've entered so far really have a release date of 1 January. Actually, in most cases, the only available information is the year. Oracle, however, is very particular about the format of dates, so we need to provide a day and a month, as well. (In addition, hours and minutes are implied, so this really means zero hours, zero minutes, zero seconds after midnight on 1 Jan 1989.) We are essentially setting a convention that we will use only the year part of a date. Another way of representing just years would be to use a character field.
One problem with using an INSERT statement that doesn't specify a column list is that the table might change, and the statement will no longer be valid. If a column were added to the CD_COLLECTION table, for example, the previous INSERT statements would fail with the complaint from Oracle of not enough values. It is usually better to write INSERT statements with an explicit column list:
INSERT INTO CD_COLLECTION (CD_ID, ARTIST, ALBUM_TITLE)
VALUES(CD_ID_SEQUENCE.NEXTVAL, 'The Breeders', 'Last Splash');
Notice that this INSERT statement has only the CD_ID, ARTIST, and ALBUM_TITLE columns and the order in which the values appear corresponds to the statement's column list, not the table as listed by the DESCRIBE command.
Updating Records
After a record has been entered, we may wish to change it or add more information to it. The SQL UPDATE statement is used to add information to existing records. The basic format is:
UPDATE tablename SET column_name1=value1, column_name2=value2, ...
[WHERE condition]
The WHERE clause is optional and can be used to select which record or records are to be updated. If we omit it, all records will be updated. WHERE clauses can be very complicated and powerful, as we will see when we examine queries, but for now, we can get by with knowing just the basics. WHERE is followed by a logical expression; every record for which this expression is true is updated. We'll see a complete list of logical operators later, but for now, we need to know only that the comparison operator for equality is the equal sign (=). (It is distinguished from the assignment operator, which is also an equal sign, by context.) We also need to know that we can combine logical expressions with the Boolean operators AND and OR.
In the last two examples of the INSERT statement, we deliberately omitted some information. We'll correct that now. To identify a record in a table uniquely, we need to specify either its primary key or a set of columns that we know will identify the record uniquely. In the CD_COLLECTION table, we can use the ALBUM_TITLE and the ARTIST columns. (You may remember that these two columns previously served as a natural primary key.) The WHERE clause to identify the Pixies album, "Doolittle," is:
WHERE ARTIST='The Pixies' AND ALBUM_TITLE='Doolittle'
We can use this clause in an UPDATE statement to supply the LABEL, which we previously specified as NULL:
UPDATE CD_COLLECTION SET LABEL='Elektra'
WHERE ARTIST='The Pixies' AND ALBUM_TITLE='Doolittle';
There is more information to add to the Breeders album, but the format is similar:
UPDATE CD_COLLECTION SET RELEASE_DATE='1-JAN-1993',
COUNTRY='USA', LABEL='4AD/Elektra'
WHERE ARTIST='The Breeders'
AND ALBUM_TITLE='Last Splash';
Used without a WHERE clause, an UPDATE statement will change the specified field or fields for all records. The following statement will change the COUNTRY column to USA for every record in the CD_COLLECTION table:
UPDATE CD_COLLECTION SET COUNTRY='USA';
As it happens, the COUNTRY column for all the albums so far was USA anyway, so no harm has been done by this command.
Deleting Records
The DELETE statement for deleting records from the database is similar to the UPDATE statement in that it takes an optional WHERE clause for specifying which records to delete:
DELETE FROM tablename
[WHERE condition]
Like the UPDATE command, omitting the WHERE clause means the command will be performed on all records: All records will be deleted. It is frightening—but worth remembering—how easy it is to delete all records.
Let's suppose that these are the songs in the SONGS table:
SQL> SELECT SONG_TITLE FROM SONGS;
SONG_TITLE
------------------------------------------------
Black Sheets of Rain
Crystal
Don't want to know if you are lonely
I don't know for sure
If we wanted to delete I don't know for sure we would enter the following DELETE statement (remembering to use two single quotes for the literal single quote in the title):
SQL> DELETE FROM SONGS
2 WHERE SONG_TITLE='I don''t know for sure';
1 row deleted.
Listing the songs again:
SQL> SELECT SONG_TITLE FROM SONGS;
SONG_TITLE
-------------------------------------------------
Black Sheets of Rain
Crystal
Don't want to know if you are lonely
Now let's see what albums are in the CD_COLLECTION table and delete one of them:
SQL> SELECT ALBUM_TITLE FROM CD_COLLECTION;
ALBUM_TITLE
------------------------------------------------------
Black Sheets of Rain
Candy Apple Grey
Horses
Doolittle
Last Splash
SQL> DELETE FROM CD_COLLECTION
2 WHERE ALBUM_TITLE='Candy Apple Grey';
1 row deleted.
When we created the SONGS table, you may have noticed that we added the ON DELETE CASCADE option to the foreign key constraint, as we suggested in the section on DDL. This means that when a parent record is deleted from the CD_COLLECTION table, the children records in the SONGS table are deleted, as well. If we now list the SONGS table, we'll see that the songs from this album have silently been removed, as well:
SQL> SELECT SONG_TITLE FROM SONGS;
SONG_TITLE
-----------------------------------------------------
Black Sheets of Rain
Querying Data
The most important statement in SQL is the one we use to query tables, the SELECT statement. At its simplest, it can be used to select data from a single table, as we've already seen in previous examples. However, because of the many ways it can be used to combine tables and conditions, it has a versatility that we can only begin to explore here.
|
We'll be using the following table for this section:
|
FRUIT COLOR QUANTITY PRICE PICKED
---------- ---------- -------- ----- ---------
Apple Green 12 .5 12-SEP-02
Apple Red 12 .5 15-SEP-02
Mango Yellow 10 1.5 22-SEP-02
Mangosteen Purple 5 2 25-SEP-02
Durian 2 15
Orange Orange 10 1 28-AUG-02
These are the commands needed to create this table:
CREATE TABLE FRUITS (
FRUIT VARCHAR2(12),
COLOR VARCHAR2(12),
QUANTITY NUMBER,
PRICE NUMBER,
PICKED DATE
);
INSERT INTO FRUITS VALUES('Apple', 'Green', 12, 0.50,
 '12-Sep-2002');
INSERT INTO FRUITS VALUES('Apple', 'Red', 12, 0.50,
'15-Sep-2002');
INSERT INTO FRUITS VALUES('Orange', 'Orange', 10, 1.50,
'28-Aug-2002');
INSERT INTO FRUITS VALUES('Durian', 'NULL', 2, 15.00, NULL);
INSERT INTO FRUITS VALUES(NULL, NULL, NULL, NULL, NULL);
|
This is the basic format of the SELECT statement:
SELECT select_list
FROM table_name
[WHERE condition]
When executed, the SELECT statement returns one or more rows, called the result set. If we don't specify a WHERE clause, it simply returns one row for every row in the table.
The select list specifies the columns that are returned in the result set. The simplest form of the column list is an asterisk, which represents all the columns. This statement, therefore, returns all the columns and rows of the table:
SQL> SELECT * FROM FRUITS;
FRUIT COLOR QUANTITY PRICE PICKED
---------- ---------- ---------- ---------- ---------
Apple Green 12 .5 12-SEP-02
Apple Red 12 .5 15-SEP-02
Mango Yellow 10 1.5 22-SEP-02
Mangosteen Purple 5 2 25-SEP-02
Durian 2 15
Orange Orange 10 1 28-AUG-02
6 rows selected.
Tables sometimes contain columns that we are not interested in. We can select the columns we want displayed by specifying them in the select list:
SQL> SELECT FRUIT, PRICE FROM FRUITS;
FRUIT PRICE
---------- ----------
Apple .5
Apple .5
Mango 1.5
Mangosteen 2
Durian 15
Orange 1
6 rows selected.
Select list items can include not just column names, but also expressions, including combinations of columns, SQL functions, and literal values. For example, we can calculate the total value of each type of fruit we have by multiplying the quantity by the price:
SQL> SELECT FRUIT, QUANTITY*PRICE FROM FRUITS;
FRUIT QUANTITY*PRICE
---------- --------------
Apple 6
Apple 6
Mango 15
Mangosteen 10
Durian 30
Orange 10
6 rows selected.
We can also change the name of a column by giving it a column alias. Oracle lets us do this by simply following the column name or expression with the alias:
SELECT FRUIT, QUANTITY*PRICE VALUE FROM FRUITS;
Standard SQL, however, uses the keyword AS to indicate the alias. Because Oracle supports this as well, we'll use that instead, in an effort to remain as standard as possible:
SQL> SELECT FRUIT, QUANTITY*PRICE AS VALUE FROM FRUITS;
FRUIT VALUE
---------- ----------
Apple 6
Apple 6
Mango 15
Mangosteen 10
Durian 30
Orange 10
6 rows selected.
Column aliases are important, not because they provide us with nicer headings in SQL*Plus but because later, they will provide us with a way of accessing the value of SQL expressions and functions in Java, particularly when we use SQLJ.
The WHERE Clause
In the examples we have just seen, the SELECT statement returns one row of results for every row it finds in the table. One of the more powerful and commonly used abilities of SQL is to specify criteria for selecting subsets of the data in a table. The WHERE clause is used to set a condition that each row must meet in order to be included in the result set.
A condition can be a simple condition with a single comparison, such as:
FRUIT='Apple'
A condition can also be a compound condition, including multiple single conditions joined logically using AND or OR:
FRUIT='Apple' AND COLOR='Red'
Comparison Operators
The following comparison operators are available in Oracle SQL:
Table 3-1. Comparison Operators in Oracle SQL
=
|
Equivalent
|
>
|
Greater than
|
>=
|
Greater than or equal to
|
<
|
Less than
|
<=
|
Less than or equal to
|
<>, !=
|
Not equivalent
|
BETWEEN…AND
|
In range (closed interval)
|
IN
|
In set
|
IS NULL
|
Is NULL
|
IS NOT NULL
|
Is not NULL
|
LIKE
|
String comparison
|
The first set of operators should be familiar from Java. But unlike Java, where they can be used only with primitive types, in SQL, the comparison operators can also be used with any of the basic datatypes or expressions, including strings and dates.
There are two other differences between the SQL operators and Java. The first is that the equality comparison and assignment operators, both represented by a single equal sign (=), are the same in SQL. Which is meant is determined by context. The second is that the standard SQL inequality operator is represented by angle brackets (<>), though Oracle also supports the Java-style (!=).
The last set of operators may be unfamiliar. BETWEEN…AND and IN are a kind of wordy shorthand for comparisons that you can also perform using the other operators. BETWEEN…AND is used to compare a value with a range. IN is used to find whether a value is a member of a set of values.
The next two, IS NULL and IS NOT NULL, are necessary to test specifically for the presence or absence of a NULL value. They are necessary because when NULL is compared with other values, the result is neither TRUE nor FALSE, but NULL. Because NULL represents an unknown value, the result of a comparison remains unknown, as well. This is true even (or maybe especially) if both values being compared are NULL. It may seem counterintuitive, but the condition NULL=NULL is neither TRUE nor FALSE, it is NULL.
The final comparison operator, LIKE, is a special comparison operator for strings. Unlike the equivalence (=) operator, LIKE also supports wildcards.
Comparisons with Numbers
Comparisons are most frequently done with numbers, so we'll begin by very briefly looking at some examples comparing numbers—familiar territory for a Java programmer.
To find a specific value in the database, we use the equivalence operator—the equal sign. The following query finds any fruit with a price of 1:
SQL> SELECT FRUIT, PRICE
2 FROM FRUITS WHERE PRICE = 1;
FRUIT PRICE
---------- ----------
Orange 1
We don't need to limit our comparisons to simple values; we can also use expressions. For example, we can search for any fruits with a combined value, QUANTITY*PRICE, greater than 10:
SQL> SELECT FRUIT, QUANTITY*PRICE
2 FROM FRUITS
3 WHERE QUANTITY*PRICE > 10;
FRUIT QUANTITY*PRICE
---------- --------------
1 Mango 15
2 Durian 30
We can also select records that are within a given range. BETWEEN… AND is used to select a closed interval—one that includes the end points. For example, the range BETWEEN 1 AND 15 includes both 1 and 15, as the following query demonstrates:
SQL> SELECT FRUIT, PRICE
2 FROM FRUITS
3 WHERE PRICE BETWEEN 1 AND 15;
FRUIT PRICE
---------- ----------
Mango 1.5
Mangosteen 2
Durian 15
Orange 1
Using BETWEEN…AND is exactly the same as using the inequality operators (>=) and (<=):
SQL> SELECT FRUIT, PRICE
2 FROM FRUITS
3 WHERE PRICE >= 1 AND PRICE <= 15;
FRUIT PRICE
---------- ----------
Mango 1.5
Mangosteen 2
Durian 15
Orange 1
|
Using inequality operators is more flexible than using the BETWEEN…AND syntax, because we can choose whether to include either, both, or neither of the end points.
|
To select an open interval, one that excludes the end points, we can use (>) and (<). The following searches for fruit with prices between $1 and $15, not including $1 and $15—finding none, of course:
SQL> SELECT FRUIT, PRICE
2 FROM FRUITS
3 WHERE PRICE > 1 AND PRICE < 15;
FRUIT PRICE
---------- ----------
Mango 1.5
Mangosteen 2
Half-open intervals are important for sorting things into categories; otherwise, you end up with values that don't belong in any category. For example, we bought these fruits from a grower who gives us a discount, depending on the quantity of each type of fruit purchased. Someone purchasing less than 5 gets no discount, someone purchasing at least 5 but less that 10 gets a 10% discount, someone purchasing at least 10 but less than 50 gets a 20% discount, and someone purchasing 50 or more gets a 20% discount.
These ranges are:
No discount:QUANTITY < 5
10%: QUANTITY >= 5 AND QUANTITY < 10
20% QUANTITY >= 10 AND QUANTITY < 50
30% QUANTITY >= 50
There are two ways to calculate the discount for each type of fruit: either with a somewhat complex SQL statement or procedurally, as separate SQL statements, using Java, for example. SQL can be extraordinarily powerful, and a single statement can replace many lines of code. Sometimes, however, that statement can be quite complex and difficult to compose and debug. If it can't be solved with a simple query, programmers tend to use a procedural approach to combine multiple simple queries instead. This is usually inefficient for three reasons: first, using multiple queries to arrive at a single result means multiple round trips between the client application and the database; second, the database can perform data-related operations, such as searching and sorting, much more efficiently than a procedural program (not to mention that database servers tend to be much more powerful than client machines); and finally, if the operation is performed in the database, it is often possible to improve the performance even further, using indexes and other optimization techniques.
Programmers often find it surprising how much can be accomplished with an SQL statement. We don't mean to suggest that programmers need to become SQL experts, only that we need to be aware of the possibilities as much as possible. That way, we can make an informed decision about whether we should find a database expert to help us with the query, do the research necessary to write the query ourselves, or resort to a procedural approach—perhaps because, in fact, that is the only way to do it.
As we'll see in the next chapter, the SQL solution in this case, using a union, is actually quite straightforward.
|
Simple String Comparisons
String comparisons are performed using the numeric value of the characters. These values are determined by the database character set—something that we selected when we created our database. We can generally assume that the character set is compatible with ASCII, as long as we limit ourselves to the Latin characters, without diacritical marks. The decimal values of the numbers and letters, for example, are found in Table 3-2.
Table 3-2. Decimal Values of ASCII Numbers and Letters
0–9
|
48–39
|
A–Z
|
65–90
|
a–z
|
97–122
|
This limits the usefulness of making simple string comparisons for two related reasons: First, it defies intuition that 'Zoo' < 'animal'. Second, and more generally, it fails to take into account cultural conventions, such as rules regarding diacritics and non-Western European characters. We could almost go as far as to say that comparing two strings with the inequality operators (>), (>=), (<), and (<=) is usually meaningless. A better way to compare strings in a linguistically meaningful way is to use the NLSSORT function, which we will cover in the next chapter.
The most useful simple comparisons for strings are done with the equality and inequality operators "=" and "<>". They can be used to select or omit specific records. For example, to find the price of apples, we can query the database:
SQL> SELECT FRUIT, COLOR, PRICE
2 FROM FRUITS
3 WHERE FRUIT='Apple';
FRUIT COLOR PRICE
---------- ---------- ----------
Apple Green .5
Apple Red .5
Supposing we have an aversion to green fruits, we could also query to find out which fruits are not green:
SQL> SELECT FRUIT, COLOR, PRICE
2 FROM FRUITS
3 WHERE COLOR<>'Green';
FRUIT COLOR PRICE
---------- ---------- ----------
Apple Red .5
Orange Orange 1
The comparison operators, when used with strings, have the drawback that we must match case correctly. In order for queries to work reliably, we need to make sure that our text strings are stored in a consistent manner. There are SQL functions that we can use to convert strings in the database to all upper- or lowercase, so we can enter our criterion in all upper- or lowercase to find a case-insensitive match:
SQL> SELECT FRUIT, COLOR, PRICE
2 FROM FRUITS
3 WHERE UPPER(COLOR)<>'GREEN'
4 ;
FRUIT COLOR PRICE
---------- ---------- ----------
Apple Red .5
Orange Orange 1
We'll see more about functions later. But keep in mind that, for large tables, we need to be careful when using functions in complex WHERE clauses.
String Comparisons with Wildcards
We've seen that we can compare character strings for equality, using the same comparison operators that we use for dates and numbers . However, this works only if we want to compare entire string s for exact matches. SQL provides a special comparison operator, LIKE, which allows us to match portions of strings by u sing wild card characters as placeholders in pattern strings to perform the comparison. There are two wildcard characters in SQL, underscore and percent, as shown in Table 3-3:
Table 3-3. Wildcard Characters in SQL
_
|
Matches any one single character
|
%
|
Matches any number of occurrences (including zero) of any character
|
These are similar to the wildcards (?) and (*), respectively, used in the DOS and Unix environments.
The underscore (_), is easiest to understand. To match a pattern string, one character—any character—must appear wherever the (_) appears. Any literals in the rest of the string (anything except the percent symbol, in other words) must appear exactly as it appears in the pattern strings. Table 3-4 shows some examples:
Table 3-4. Examples of the Underscore Wildcard
'_'
|
Any single letter string
|
'__'
|
Any string two letters long
|
'_BC'
|
Any string three letters long, ending in BC
|
'A_C'
|
Any string three letters long, beginning with A and ending with C
|
'_A_'
|
Any string three letters long, with middle letter A
|
The percent sign (%) is probably harder to explain than it is to understand. It can represent any number of any character or characters, including none. However, if there are any literals in the string or (_) in the string, these must be matched, as well. For example (Table 3-5):
Table 3-5. Examples of the Percent Sign Wildcard
'%'
|
Any string, but not NULL
|
'%A'
|
Any string that ends with A
|
'A%'
|
Any string that begins with A
|
'%A%'
|
Any string that contains the letter A
|
'_%A%'
|
Any string that contains the letter A, except as the first letter
|
We'll try a few sample queries. For the first one, we'll select all fruits that begin with Mango:
SQL> SELECT FRUIT FROM FRUITS WHERE FRUIT LIKE 'Mango%'
FRUIT
----------
Mango
Mangosteen
Next, we'll look for those that contain the letters an
SQL> SELECT FRUIT FROM FRUITS WHERE FRUIT LIKE '%an%';
FRUIT
----------
Mango
Mangosteen
Durian
Orange
Next, those that contain the letters an, except at the end:
SQL> SELECT FRUIT FROM FRUITS WHERE FRUIT LIKE '%an%_';
FRUIT
----------
Mango
Mangosteen
Orange
Finally, those that end with the letters an:
SQL> SELECT FRUIT FROM FRUITS WHERE FRUIT LIKE '%an';
FRUIT
----------
Durian
Date and Time Comparisons
Date and time comparisons are similar to comparisons with numbers because underlying every date and time is a number. The only thing that makes this a little more difficult is that, in Oracle, there is a single datatype, DATE, that represents both date and time with a single number.
If we want to compare a date column with another date, the easiest thing to do is use a string literal in the default date format and let Oracle perform the conversion for us behind the scenes, like this:
SQL> SELECT FRUIT, PICKED FROM FRUITS WHERE PICKED>'20-SEP-2002';
FRUIT PICKED
---------- ---------
Mango 22-SEP-02
Mangosteen 25-SEP-02
We can get the current date and time in Oracle by using the pseudo-column, SYSDATE. A pseudo-column is an Oracle function that we use as though it were a column that automatically exists in any table—we can use it anywhere we'd use any of the table's real column names. Now let's suppose that today is 25 September 2002. This query will list all of the fruits in our table, the day they were picked, and the current system date:
SQL> SELECT FRUIT, PICKED, SYSDATE FROM FRUITS;
FRUIT PICKED SYSDATE
---------- --------- ---------
Apple 12-SEP-02 25-SEP-02
Apple 15-SEP-02 25-SEP-02
Mango 22-SEP-02 25-SEP-02
Mangosteen 25-SEP-02 25-SEP-02
Durian 25-SEP-02
Orange 28-AUG-02 25-SEP-02
7 rows selected.
It looks as though one of the fruits, mangosteen, was picked today: SYSDATE and PICKED are the same. But if we query for fruits picked today using SYSDATE, we won't find any. When we entered the PICKED dates, we entered the date using the default date format that doesn't have a time part. The time defaulted to 12:00:00 a.m. SYSDATE, the current date and time, does have a time part, even though only the date part displays by default:
SQL> SELECT FRUIT, PICKED FROM FRUITS
2 WHERE PICKED=SYSDATE;
no rows selected
To remove the time part of an Oracle DATE, we can use the TRUNC function. This way, we can compare the dates in the table with today's date, disregarding any hours, minutes, or seconds:
SQL> SELECT FRUIT, PICKED FROM FRUITS
2 WHERE PICKED=TRUNC(SYSDATE);
FRUIT PICKED
---------- ---------
Mangosteen 25-SEP-02
Logic in SQL: AND, OR, NOT, and NULL
Now that we've seen how to create simple conditions, we'll look at how conditions can be negated using NOT or combined with AND or OR to form a compound condition. We'll also look at how the unknown value, NULL, complicates things.
In SQL, there are three logical values, TRUE, FALSE, and NULL. Every condition, simple or compound, evaluates to one of these three values. In a WHERE clause, if this condition evaluates to TRUE, the row is returned if it's part of a SELECT statement, for example. If it's FALSE or NULL, it is not.
NOT
FALSE and NULL are not the same, though. When we negate FALSE, we get TRUE. But when we negate NULL, we still get NULL. Table 3-6 is the truth table for NOT:
Table 3-6. Truth Table for NOT
TRUE
|
FALSE
|
FALSE
|
TRUE
|
NULL
|
NULL
|
If we query the FRUIT table with the condition COLOR='Green', we can see that only one row meets this criterion:
SQL> SELECT FRUIT, COLOR FROM FRUITS
2 WHERE COLOR='Green';
FRUIT COLOR
---------- ----------
Apple Green
Now let's try the negation of this, NOT(COLOR='Green'):
SQL> SELECT FRUIT, COLOR FROM FRUITS
2 WHERE NOT(COLOR='Green');
FRUIT COLOR
---------- ----------
Apple Red
Mango Yellow
Mangosteen Purple
Orange Orange
If you're used to regular Boolean logic, this result may seem strange, because between the fruits that are green and the fruits that are not, there appears to be some fruit missing. The entry for durian doesn't appear in either of these tables, because its color is unknown, NULL.
AND
AND is used to combine two conditions. In order to satisfy the combined condition, both must be true. If both are TRUE, the combination is TRUE. If either (or both) are FALSE, the combination is FALSE. If either (or both) are NULL, the combination is NULL.
Table 3-7 is the AND truth table:
Table 3-7. Truth Table for AND
TRUE
|
TRUE
|
FALSE
|
NULL
|
FALSE
|
FALSE
|
FALSE
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
Here is a query combining conditions with AND:
SQL> SELECT FRUIT, COLOR FROM FRUITS
2 WHERE FRUIT ='Apple' AND COLOR='Green';
FRUIT COLOR
---------- ----------
Apple Green
Only one entry, obviously, is both green and an apple. We already know that the other fruits are either not apples or not green. This doesn't demonstrate anything about the NULL entries in the truth table, however.
Because we previously demonstrated that NOT(NULL) evaluates to NULL, we can demonstrate that TRUE AND NULL evaluates to NULL in the following query:
SQL> SELECT FRUIT, COLOR FROM FRUITS
2 WHERE FRUIT='Durian' AND NOT(COLOR='Green');
no rows selected
Because we know there is a row that satisfies the condition FRUIT='Durian', the condition NOT(COLOR='Green') is either FALSE or NULL for that row. If it's FALSE and we negate it again, it will be TRUE, and the following query should return this row:
SQL> SELECT FRUIT, COLOR FROM FRUITS
2 WHERE FRUIT='Durian' AND NOT(NOT(COLOR='Green'));
no rows selected
Because it still doesn't return anything, we know that the original condition, NOT(COLOR='Green') was NULL. We still don't know, though, whether the row wasn't returned because the combined condition is NULL or because it is FALSE. We can find that out by negating the combined condition and seeing whether we get the durian in the results:
SQL> SELECT FRUIT, COLOR FROM FRUITS
2 WHERE NOT(FRUIT='Durian' AND NOT(COLOR='Green'));
FRUIT COLOR
---------- ----------
Apple Green
Apple Red
Mango Yellow
Mangosteen Purple
Orange Orange
If the combined condition had originally evaluated to FALSE, negating it would have made it TRUE. Because we still don't get the durian row in the results, we know that TRUE AND NULL evaluate to NULL.
OR
OR is also used to combine two conditions. In order to satisfy the combined condition, one (or both) must be TRUE. If both are FALSE, the result is FALSE. If one is UNKNOWN, and the other is FALSE or UNKNOWN, the result is UNKNOWN.
Table 3-8 is the OR truth table:
Table 3-8. Truth Table for OR
TRUE
|
TRUE
|
TRUE
|
TRUE
|
FALSE
|
TRUE
|
FALSE
|
UNKNOWN
|
UNKNOWN
|
TRUE
|
UNKNOWN
|
UNKNOWN
|
Here is a sample query with OR:
SQL> SELECT FRUIT, COLOR FROM FRUITS
2 WHERE FRUIT ='Durian' OR COLOR='Green';
FRUIT COLOR
---------- ----------
Apple Green
Durian
We can see here, from the presence of the durian row, that if one of the conditions is NULL and the other is TRUE, the combined result is still TRUE.
| 
