Lesson: Reading Data from Multiple Tables- Part 2
112
Lesson Overview
This lesson covers how sub-queries can be used to retrieve data from several tables in a single statement.
Lesson Objectives
After completing this lesson, you will be able to:
• Use sub-queries to query data from multiple tables in a single select statement.
• Explain the difference bet\veen uncorrelated and correlated sub-queries.
Business Example
Nested Queries
Figure 235: Nested Queries
• In addition to UNION and JOIN, Nested Queries also provide the option of
reading from multiple tables and views.
• Nested Queries contain a so called "Sub Query" .
What is a sub query?
Figure 236: Sub Query
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Data from Multiple Tables- Part 2
+ A sub query is a query (SELECT-FROM-WHERE statement) that is used in another query (SELECT-FROM-WHERE statement).
+ The "other Query" - containing the sub query - Is called the "outer query".
+ In this context the Sub Query Is also referred to as "Inner query".
Outer Query
'Wiesloch');
SELECT PlateNumber, Brand, Color
FROM Car Query
WHERE owner IN ( SELECT OwnerID
FROM Owner
WHERE City =
Figure 237: Sub Query
When is a sub query useful?
Figure 238: Sub Query A sub query is useful if ...
•
•
•
It makes the SELECT statement more "readable"- for example, because the use of nested queries is the "obvious way"
The SELECT statement has a better performance -however, with a "perfect"
optimizer, this should not be the case.
The formulation of the SELECT statement is not possible (or only extremely cumbersome) without a sub query - which can be particularly the case for aggregate expressions.
What is the difference between an uncorrelated and a correlated sub
query? Figure 239: Sub Query
Unit 5: Reading Data From Multiple Tables Part II
114
An uncorrelated sub query makes no reference to the outer query.
SELECT *
FROM Car
WHERE Owner IN (SELECT OwnerID FROM Owner
WHERE City = 'Wiesioch');
A correlated sub query refers to the outer query.
SELECT *
FROM Car c
WHERE EXISTS (SELECT *
FROM owner o
WHERE o.OwnerID = c.Owner
AND o.City = 'Wiesioch');
Figure 240: Sub Query
Uncorrelated sub queries
Figure 241: Sub Query
SELECT Column, Column, Column FROM Table
WHERE Column IN (SELECT Column
FROM Table
WHERE Condition);
Figure 242: Uncorrelated Sub Query
You can use IN, if the outer value should be included in the result set of the sub query.
>Which cars are registered to an owner from Wiesloch?
SELECT *
FROM Car
WHERE Owner IN (SELECT OwnerID FROM Owner
WHERE City= 'Wiesioch');
CARIO PLATENUMBER BRAND COLOR HP OWNER
FOl HO-V 106 Fiat red 75 H06
Fl9 HO-VW 2012 VW black 125 HOl
Figure 243: Uncorrelated Sub Query
© 2014 SAP AG or an SAP affiliate company.
All rights reserved.
HA150
HA150 Lesson: Data from Multiple Tables- Part 2
You can use= ANY, if the outer value should match any result value of the sub query.
;Which cars are registered to an owner from Wiesloch?
SELECT *
FROM Car
WHERE Owner - ANY (SELECT OwnerID
FROM owner
WHERE City= 'Wiesloch');
CARID PLATENUMBER BRAND COLOR HP OWNER
FOl HD-V 106 Fiat red 75 H06 Fl9 HD-VW 2012 vw black 125 HOl
Figure 244: Uncorrelated Sub Query
"= ANY" is equivalent to "IN".
So, where is the value of"= ANY"?
Figure 245: Uncorrelated Sub Query
= ANY is equivalent to IN,
but you can use ANY with other comparison operators:
= ANY The external value matches any value of the sub query.
< ANY The external value is less than any value of the sub query.
<= ANY The external value is less or equal than any value of the sub query.
> ANY The external value is greater than any value of the sub query.
>= ANY The external value is greater or equal than any value of the sub query.
<> ANY The external value is different to any value of the sub query.
Unit 5: Reading Data From Multiple Tables Part II HA150
116
You can use > ANY, if the external value should be greater than any value of the sub query.
:;.. Which officials have more than the minimum overtime hours?
SELECT Name, overtime FROM Official
WHERE Overtime > ANY (SELECT overtime FROM Official) ,
Figure 247: Uncorrelated Sub Query
NAME OVERTIME
Ms c 20
Mr F 18
Ms G 22
You can use > instead of > ANY, if the sub query results In only a single value.
) Which officials have more than the minimum over time hours?
SELECT Name, overtime FROM Official
WHERE Overtime > (SELECT MIN (overtime) FROM Official) ;
Figure 248: Uncorrelated Sub Query
NAME OVERTIME
Ms c 20 Mr F 18
Ms G 22
You can use < ALL, If the outer value should be less than all result values of the sub query (other comparison operators are possible)
= ALL The external value matches each result value of the sub query.
(This option is quite useful, as the sub query result can contain duplicates.)
< ALL The external value is less than all values of the sub query.
<= ALL The external value is less or equal than all values of the sub query.
> ALL The external value is greater than all values of the sub query.
>= ALL The external value is greater or equal than all values of the sub query.
<> ALL The external value is different to all values of the sub query.
Figure 249: Uncorrelated Sub Query
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Data from Multiple Tables- Part 2
You can use <= AL.L, if the outer value should be less or equal than all
result values of the sub query (other comparison operators are possible)
:.>Which officials have the fewest overtime hours?
SELECT Name, overtime
FROM Official
WHERE overtime <= ALL (SELECT Overtime
FROM Officia.l
NAME OVERTIME
Mr A 10 Mr B 10 Mr E 10
WHERE Overtime IS NOT NULL) ;
Figure 250: Uncorrelated Sub Query
You can use <= instead of <= ALL, if the sub query results in only a single value. ;>Which officials have the fewest overtime hours?
»For the example below = would be possible instead of as well.
SELECT Name, overtime
FROM Official
WHERE overtime <= (SELECT MIN(Overtime) FROM Official) ; NAME OVERTIME --- Mr A 10 Mr B 10 Mr E 10
Unit 5: Reading Data From Multiple Tables Part II HA150
118
You can use functions In a sub query.
)>Which owners were born in the same year as the youngest owner?
SELECT *
FROM owner
WHERE YEAR(Birthday) - (SELECT MAX(YEAR(Birthday)) FROM Owner) ; OWNERID NAME --- ---- HOS Mr x H09 Ms y HlO Mr z
Figure 252: Uncorrelated Sub Query
J> To whom is (at least) one blue car registered?
SELECT Name FROM Owner BIRTHDAY --- 1986-08-30 1986-02-10 1986-02-03
WHERE OwnerID IN (SELECT Owner FROM Car CITY --- Wall.dorf Sinshei.m. Ladenburg
WHERE Color - 'blue');
Figure 253: Uncorrelated Sub Query
You can use multiple columns in the sub query.
,. To whom within the EU is (at least) one blue car registered?
SELECT Name
FROM owner EU
NAME
SAP AG
WHERE (Country, OwnerID) IN (SELECT Country, Owner FROM Car EU
WHERE Color = 'blue');
Figure 254: Uncorrelated Sub Query
© 2014 SAP AG or an SAP affiliate company.
All rights reserved.
NAME
SAP AG
Senor Q
HA150 Lesson: Data from Multiple Tables- Part 2
You can use the same table in both the FROM clause of the outer query and in the FROM clause of the sub query.
;.What is the name of Mr A's manager?
SELECT Name FROM Offic.ial
WHERE PNr IN (SELECT Manager
FROM Official
WHERE Name = 'Mr A');
Figure 255: Uncorrelated Sub Query
The WHERE clause of a sub query can contain another sub query.
>Who are the owners of stolen cars?
SELECT Name FROM Owner WHERE OwnerID IN ( SELECT Owner FROM Car WHERE PlateNumber IN Ms D
( SELECT PlateNumber FROM Stolen )
) ;
Figure 256: Uncorrelated Sub Query
You can combine JOIN and Sub Query.
>To whom is the most powerful car per brand registered?
SELECT DISTINCT Brand, Name
FROM Owner RIGHT OUTER JOIN Car
ON OwnerID = Owner
WHERE (Brand, HP) IN
( SELECT Brand, MAX (HP)
FROM Car GROUP BY Brand ) BMND Mr v Ms w Ms y NAMli --- --- Audi Ml: v - Ma u - SAP AG riat Ma w Horcedea SAP AG Opel ? Renault IKEA Skoda SAP M vw IKEA
Unit 5: Reading Data From Multiple Tables Part II HA150
120
• A sub query is usually part of the WHERE clause.
• But you can also use a sub query in the FROM clause.
> Which blue cars have less than 120 HP?
SELECT *
FROM (SELECT PlateNumber, HP AS Power FROM Car
WHERE Color = 'blue') WHERE Power < 120;
Figure 258: Uncorrelated Sub Query
+ A sub query is usually part of the WHERE clause.
• But you can also use a sub query in the SELECT clause.
PLATENUMBER POWER
HD-ML 3206 116
;.. What is the HP deviation of each car when compared to the most powerful yellow
car?
SELECT CarID, Brand, (SELECT MAX (HP)
FROM Car
WHERE Color = 'yellow') - HP AS Deviation
FROM Car
ORDER BY 3 DESC;
Figure 259: Uncorrelated Sub Query
CAn!O
FOl F18 F09
F06
Correlated sub queries
Figure 260: Sub Query
© 2014 SAP AG or an SAP affiliate company.
All rights reserved.
BAAN'D OJ!:Vl:A'r!ON .... _ .. _ .. _ --- .. Fiat Renaul.t Skoda Audi 2014
HA150 Lesson: Data from Multiple Tables- Part 2
SELECT Column, Column, Column
FROM Table
WHERE EXISTS (SELECT *
FROM Table
WHERE Condition);
Figure 261: Correlated Sub Query
• A correlated sub query refers to the outer query.
+ Using EXISTS you can check that the query result of the sub query is not empty.
,. Which vehicles are registered to an owner from Wiesloch?
SELECT *
FROM Car c
WHERE EXISTS (SELECT *
FROM Owner o
WHERE o.City = 'Wiesloch' AND o.OwnerID = c.Owner);
CARID PLATENUMBER BRAND COLOR HP OWNER
FOl HD-V 106 Fiat red 75 H06
Fl9 llD-VW 2012 VW black 125 HOl
Figure 262: Correlated Sub Query
Even with a correlated sub query, you can omit tuple variables if the column names are unique.
:;.What vehicles are registered to an owner from Wiesloch?
SELECT * FROM Car
WHERE EXISTS (SELECT *
FROM Owner
WHERE City= 'Wiesloch' AND OwnerID = Owner);
CARID PLATENUMBER BRAND COLOR HP --- --- --- ---
FOl llD-V 106 Fiat red 75
F19 llD-VW 2012 vw black 125
Figure 263: Correlated Sub Query
OWNER
-----
HOS
122
Using NOT EXI s TS you can check if the result of the sub query is em p�
>-Which cars are not reported as stolen?
SELECT Car
FROM Car c
WHERE NOT EXISTS (SELECT *
FROM Sto1en s
WHERE s.P1ateNumber -
c.P1ateNumber);
Figure 264: Correlated Sub Query
F02 F03 F04 FOS F01 F08 F09 FlO Fll F12 Fll F14 FlS Fl6 F18 F19 no
You can use the same table in both the FROM clause of the outer query and the FROM clause of the correlated sub query.
i>Which vehicle has the most HP?
SELECT *
FROM Car c
WHERE NOT EXISTS (SELECT *
FROM Car c2
WHERE c2.HP > cl.HP) i
CARm PLATl!:NUMB!!R BRAND COLOR HP OWNER
F06 HD-VW 1999 Audi yellow 260 HOS
Figure 265: Correlated Sub Query
Even when using a correlated sub query, you can for example use">= ALL"
:;.How much horsepower has the most powerful car of each brand?
WHEBE cl.HP >= ALL (SELECT c2.HP
FROM Car c2 BRAl!D HP Fiat 75 BMW 184 J.tarcedas 170 Audi 260 vw 160 Renault 136 Skoda 136 Opel 120
WHEBE c2.Brand = cl.Brand);
Figure 266: Correlated Sub Query
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Data from Multiple Tables- Part 2
The WHERE clause of a correlated sub query can contain an additional correlated sub query.
;. To whom (at least) one stolen car is registered?
SELECT o.Name
FROM Owner o WHERE EXISTS
( SELECT *
FROM Car c
WHERE c.Owner = o.OwnerID
AND EXISTS ( SELECT *
FROM Stolen a
NAME
Mr v
Ms w Ms y
WHERE a.PlateNurnbor = c.PlateNumber )
) ;
Figure 267: Correlated Sub Query
You can also use a correlated sub query in the SELECT clause. >How much horsepower each car is lacking
compared to the most powerful car of the same brand?
SELECT CarID, Brand, (SELECT MAX(HP) FROM Car c2 WHERE c2.Brand
AS Difference
FROM Car
Figure 268: Correlated Sub Query
Sub Query
Using Nested Queries you also can:
• Restrict the projection list to certain columns • Explicitly rename the result columns
• Sort the query result • Use grouping
• Include aggregate expressions
• Eliminate duplicates using DISTINCT
l.Brand) - HP
C11P.ID BRAND Dll'f'2RENCE
--- --- ro1 Fiat 0 1'02 vw 40 !'OS BMW 0 1'04 Mercedes 34 ros Meroedes 0 F06 Aud.i. 0 1'07 A.ud.i 144 !'20 Audi 76
Unit 5: Reading Data From Multiple Tables Part II HA150
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Data from Multiple Tables- Part 2
Exercise 4: Exercise 4
Exercise Objectives
After completing this exercise, you will be able to:
• use sub-queries to retrieve data from several tables in a single statement
Business Example
Task:
l . Which employees \Vork for the Sales department? Use an uncorrelated sub query.
2. Which employees \vork for the Sales department? Use a correlated sub query.
3. Which employees accumulated the most remaining vacation days? Use an uncorrelated sub query with "MAX".
4. Which employees accumulated the most remaining vacation days? Use an uncorrelated sub query \vithout "MAX".
�
Unit 5: Reading Data From Multiple Tables Part II HA150
126
Solution 4: Exercise 4
Task:
1. Which en1ployees \vork for the Sales departtnent? Use an uncorrelated sub query.
a) SELECT* FROM Etnployee \.VHERE DeplD =ANY (SELECT DeplD FROM Department WHERE Name = 'Sales');
2. Which employees work for the Sales department? Use a correlated sub query. a) SELECT 1n. * FROM Employee m WHERE EXISTS (SELECT *
FROM Department a WHERE a.DepJD = m.DeplD AND a.Name= 'Sales');
3. Which employees accumulated the 1nost remaining vacation days? Use an
uncorrelated sub query with "MAX".
a) SELECT * FROM Employee WHERE RemainderDays =(SELECT MAX(RemainderDays) FROM Employee);
4. Which employees accumulated the most remaining vacation days? Use an uncorrelated sub query without "MAX".
a) SELECT * FROM Employee WHERE RemainderDays >= ALL
(SELECT RemainderDays FROM E1nployee WHERE RemainderDays IS NOT NULL);
5. Which employees have above average remaining vacation days?
a) SELECT* FROM Employee WHERE Re1nainderDays >(SELECT AVG(RemainderDays) FROM Employee);
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Data from Multiple Tables- Part 2
Lesson Summary You should now be able to:
• Use sub-queries to query data from multiple tables in a single select
statement.
Unit Summary
128
Unit Summary
You should now be able to:
• Use sub-queries to query data from multiple tables in a single select
statement.
HA150
• Explain the difference between uncorrelated and correlated sub-queries .
© 2014 SAP AG or an SAP affiliate company.
n1
Understanding NULL Values
Unit Overview
Unit Objectives
After completing this unit, you will be able to:
• Explain how to interpret NULL values in databases.
• Understand \Vhy the presences of NULL values can lead to unexpected query
results.
• Analyze if t\vo seemingly equivalent SQL statements return different results
because of NULL values.