MORE on subqueries:
Subqueries with multiple columns:
For this handout, I am using the inventory file that I set up - the data and the description are shown
below. Subqueries can be single-row subqueries where you are matching to the retrieval of a single row,
multiple-row subqueries where only one column is used in the inner select or multiple-column subqueries
where you are putting more than one column in a compound where clause.
SQL CODE:
SQL> select * from inven
2 ;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 10 0 25 25 27.95 SP BK Y200
8 rows selected.
SQL> desc inven
Name Null? Type
------------------------------- -------- ----
ITEMNO VARCHAR2(4)
ITEMNAME VARCHAR2(15)
ONHAND NUMBER(5)
ONORDER NUMBER(5)
REORDPT NUMBER(5)
COST NUMBER(6,2)
PRICE NUMBER(6,2)
DEPT CHAR(2)
ITEMCLASS CHAR(2)
LOCATION VARCHAR2(4)
With multiple-column subqueries you can have pairwise comparisons and nonpairwise comparisons. My
understanding is that a pairwise comparison says that each candidate row in the outer select statement
must match the criteria in a particular record or row that was selected by the first select. The example
below shows a pairwise comparison where I want all the records that have the same dept and itemclass as
those selected in the inner query. To clarify, I showed a second select that is a standalone version of
the inner select so that you can see the results of the inner select. As you can see, the inner select
came up with three combinations of dept and item class that met the criteria. The outer select then selects
all rows that have one of those three combinations (actually there are only two distinct combinations, the
third matches the second).
SQL CODE:
SQL> select * from inven where (dept, itemclass) in
2 (select dept, itemclass from inven where reordpt >=25 and price > 15);
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 10 0 25 25 27.95 SP BK Y200
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
SQL> select dept, itemclass from inven where reordpt >= 25 and price > 15;
DE IT
-- --
TY CH
SP BK
SP BK
In this example, I am asking for all records where the dept is in the group selected from the first inner
select and the itemclass is in the group selected by the second inner select. Again, I ran the inner
selects separately so you can see the results. The resolution of this select is that I want all rows/records
where dept is either BK, TY, or SP (results of first inner select) AND itemclass is either CH or BK (results
of second inner select). The major difference is that I am not matching to a single record, I am matching to
the results from all of the records.
SQL CODE:
SQL> select * from inven where dept in
2 (select dept from inven where reordpt >=25)
3 and itemclass in
4 (select itemclass from inven where price > 15);
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 10 0 25 25 27.95 SP BK Y200
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
7 rows selected.
SQL> select dept from inven where reordpt >=25;
DE
--
BK
BK
TY
SP
SP
SQL> select itemclass from inven where price > 15;
IT
--
CH
CH
CH
BK
BK
SQL>
1 select * from inven where dept in
2 (select dept from inven where reordpt >=25 and price > 15)
3 and itemclass in
4* (select itemclass from inven where reordpt >=25 and price > 15)
SQL> /
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 10 0 25 25 27.95 SP BK Y200
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
SQL> select dept from inven where reordpt >=25 and price > 15;
DE
--
TY
SP
SP
SQL> select itemclass from inven where reordpt >=25 and price > 15;
IT
--
CH
BK
BK
Another example: In this example, I want all rows/records where onhand and onorder are a match to
the records in itemclass CH. On the first try, all the rows that are retrieved have CH in itemclass
which indicates that there are no records that have an exact match to them. I then looked at the
entire inventory and decided to change item 3456 to have an onhand of 5 and then it would be the same
as item 1234. I did this and reran the select, this time item 3456 comes up because on hand and onorder
match one of the records with itemclass CH. Note that in this example, I had to have onhand and onorder
from the same record match.
SQL CODE:
1 select * from inven where (onhand, onorder) in
2* (select onhand, onorder from inven where itemclass='CH')
SQL> /
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
2345 Doll House 2 5 10 45 55.98 TY CH Z212
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
1212 Heidi 12 25 25 10 14.99 BK CH X112
SQL> select * from inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 10 0 25 25 27.95 SP BK Y200
8 rows selected.
SQL> update inven
2 set onhand=5 where itemno='3456';
1 row updated.
SQL> select * from inven where (onhand, onorder) in
2 (select onhand, onorder from inven where itemclass = 'CH');
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
2345 Doll House 2 5 10 45 55.98 TY CH Z212
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
3456 Net/Hoop 5 0 25 25 27.95 SP BK Y200
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
1212 Heidi 12 25 25 10 14.99 BK CH X112
6 rows selected.
SQL> select * from inven where onhand in
2 (select onhand from inven where itemclass = 'CH')
3 and onorder in
4 (select onorder from inven where itemclass = 'CH');
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
3456 Net/Hoop 5 0 25 25 27.95 SP BK Y200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
1212 Heidi 12 25 25 10 14.99 BK CH X112
6 rows selected.
I then set the onhand for item 3456 back to the original 10 and ran the nonpairwise comparison. Because
of the nature of the data, I got the same results as he pairwise comparison. I then went in and changed
item number 3456 to have an onhand of 12. This means that looking at record 34546 the onhand of 12 will
match the on hand of item 1212 (note that the onorder does not) and the onorder of 0 will match the onorder
of either 2222 or 1234. This means it will show up in the comparison. The thing to note is that the matches
are with different records, not necessarily the same record in the nonpairwise comparison.
SQL CODE:
SQL> update inven
2 set onhand=10
3 where itemno='3456';
1 row updated.
SQL> select * from inven where onhand in
2 (select onhand from inven where itemclass = 'CH')
3 and onorder in
4 (select onorder from inven where itemclass = 'CH');
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2345 Doll House 2 5 10 45 55.98 TY CH Z212
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
1212 Heidi 12 25 25 10 14.99 BK CH X112
5 rows selected.
SQL> update inven
2 set onhand=12
3 where itemno='3456';
1 row updated.
SQL> select * from inven where onhand in
2 (select onhand from inven where itemclass = 'CH')
3 and onorder in
4 (select onorder from inven where itemclass = 'CH');
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
2222 Building Blocks 4 0 15 23 27.98 TY CH Z200
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
1212 Heidi 12 25 25 10 14.99 BK CH X112
6 rows selected.
Problems with null values in a subquery:
There is a problem with null values in a subquery. If one of the value returned when the inner query is
executed is null then no rows are returned. Since all conditions that compare a null value produce a null
you should not use the NOT IN which is the same as !=ALL in the query. You can use the the NVL to get around
this. In the example below, the inner select takes all manager numbers from the table test null. I now want
a list of all rows/records where the idno is not in the group selected. Logically I should have gotton results,
but because of the way the null works, I did not. In the second example, I said that null values should be
replaced with 0000 and now I got a list of all the employees whose idno was not in the manager column. In other
words I got a list of all people that are not managers because those are the people who have a manager number
listed in the manager column.
SQL CODE:
SQL> select * from testnull;
IDNO NAME MANG DE
---- --------------- ---- --
1111 John Doe 2222 AB
1212 Kevin Adams 3333 XY
2222 Linda Richmond AB
3333 Richard Wilson XY
3456 Susan Anders 3333 XY
SQL> select *
2 from testnull
3 where idno NOT IN
4 (select mangr from testnull);
no rows selected
SQL> select * from testnull
2 where idno NOT IN
3 (select NVL(mangr,'0000') from testnull);
IDNO NAME MANG DE
---- --------------- ---- --
1111 John Doe 2222 AB
1212 Kevin Adams 3333 XY
3456 Susan Anders 3333 XY