TRANSPORTE EXPRESS - LOS TRES GRANDES RÍOS CHINOS

Delta del río Perla

LOS TRES GRANDES RÍOS CHINOS

6. TRANSPORTE EXPRESS

Lesson: Reading Data from Multiple Tables- Part 1

�

Lesson Overview

This lesson covers how data can be retrieved and combined from several tables in a single statement, using UNIONs and JOINs.

Lesson Objectives

After completing this lesson, you will be able to:

• Merge the result of several select statements using the UNION state1nent. • Co1nbine data from several tables \Vhen querying data using JOIN constn1cts. • List the various types of Joins.

• Explain the differences between the various types of Joins, and apply the

right type of Join depending on the problem.

Business Example

How can

read data from multiple

tables and views?

Figure 173: Access to multiple tables

To read data fro1n multiple database tables or views the followi11g options are available:

• Combination of results from several queries

UNION ALL vs. UNION INTERSECT

EXCEPT

• Combination of several tables (JOIN)

CROSS JOIN vs. INNER JOIN vs. OUTER JOIN Implicit JOIN vs. Explicit JOIN

• Nested queries (Sub Query)

Uncorrelated Sub Query vs. Correlated Sub Query

HA150 _Lesson: _{Data from Multiple Tables- Part}₁

Combination of results from several partial queries

Figure 174: UNION [ALL]

SELECT Column, Column, Column FROM Table

WHERE Condition

UNION ALL

SELECT Column, Column, Column FROM Table

WHERE Condition;

Figure 175: UNION [ALL]

You can combine the result tables of multiple queries using UNION [ALL]. +The individual result tables must have the same number of columns.

+The corresponding result columns must have compatible data types. +The column names of the resulting output table are based on the first SELECT statemenl

SELECT PNr, Name

FROM Official

WHERE Salary = 'A09'

UNION ALL

SELECT OwnerID, Name FROM Owner

WHERE Birthday>= 11977-05-21';

Figure 176: UNION [ALL]

PNR POl P03 P06 HOS H09 HlO NAME Mr A Ms C Mr F Mr x Ms Y Mr z

Unit 4: Reading Data From Multiple Tables Part I HA150

:.> Almost the same statement as on the previous slide - but with a changed sequence of partial queries.

• The column names of the resulting output table are based on the

f_irst SELECT statement.

SELECT OwnerID, Name FROM Owner

WHERE Birthday>= '1977-05-21' UNION ALL

SELECT PNr, Name FROM Officia1

WHERE Sa1ary = 'A09';

Figure 177: UNION [ALL]

OWNER ID --- HOB H09 HlO POl P03 P06

With UNION [ALL] you can also explicitly rename the result columns. SELECT OwnerID AS "Person ID", Name

FROM _Owner

WHERE Birthday>= '1977-05-21' UNION ALL

SELECT PNr, Name FROM Off icia1

WHERE Sa1ary = 'A09';

Figure 178: UNION [ALL]

Person ID --- HOB H09 HlO POl P03 P06 NAME Mr X Ms Y Mr Z Mr A Ms c Mr F NAME Mr x Ms Y Mr z Mr A Ms c Mr F

If the results of multiple partial queries overlap, the overall result includes duplicates

with UNION ALL.

SELECT P1ateNumber, Brand, Co1or FROM Car

WHERE Brand= 'Mercedes• UNION ALL

SELECT PlateNumber, Brand, Co1or

FROM Car _l'LATENUMBER

WHERE Co1or = 'white 1 ; ---

BRAND COLOR --- Mercede" White MercedeG l>lack { KD-AL 1002 Duplicate KD- �lM 3206 HD-MM 1977 s HD-AL 1002 KD-l·lM 1977 Mercedes white

}-

Mercedes white !*%cede" white

Figure 179: UNION ALL

HA150 Lesson: Data from Multiple Tables- Part 1

You use UNION instead of UNION ALL to ellmlnate dupllcates.

SELECT PlateNumber, Brand, Color FROM Car

WHERE Brand= 'Mercedes' UNION

SELECT PlateNumber, Brand, Color FROM Car

WHERE Color = 'white';

PLATENUMBER --- HD-AL 1002 HD-MM 3206 HD-MM 1977 Figure 180: UNION

INTERSECT returns records that exist in all query results.

SELECT PlateNumber, Brand, Color FROM Car

WHERE _Brand = 'Mercedes'

INTERSECT

SELECT PlateNumber, Brand, Color

FROM _Car

WHERE _Color ₌ _'_{white '}_;

BRAND --- Morcedos Mercedes Morcedos COLOR --- white blaclt white

PLAX!NUMl!ZR BRAND COLO!t

Figure 181: INTERSECT [DISTINCT]

HD-AL 1002 Mercedes white HD-MM 1977 Mercedes white

EXCEPT returns results from the first query which are NOT avallable In

subsequent queries.

SELECT PlateNumber, Brand, Color

FROM Car

WHERE _Brand ₌ _'Mercedes'

EXCEPT

SELECT PlateNumber, Brand, Color

FROM Car

�

Unit 4: Reading Data From Multiple Tables Part I

You can use UNION (ALL] to combine result tables of multiple partlal queries.

SELECT PlateNumber, Brand, Color

FROM Car

WHERE Brand = 'BMW' ONION

SELECT PlateNumber, Brand, Color FROM Car

WHERE Color = •yellow'

ONION PLATENUMBER

SELECT PlateNumber, Brand, FROM Car

Color ---

HD-JA 1972

WHERE Color = •orange' ; HD-MT 507

HD-MM 208

!ID-VW 1999 HD-Y 333

Figure 183: UNION [ALL]

Joining tables

Figure 184: Joining Tables

With JOIN semantics you can distinguish between:

+CROSS JOIN

+INNER JOIN

+OUTER JOIN

• LEFT OUTER JOIN

• RIGHT OUTER JOIN

• FULL OUTER JOIN

With JOIN syntax you can distinguish between:

+Implicit JOIN +Explicit JOIN

Figure 185: Joining Tables

CROSS JOIN

(Cartesian Product)

Figure 186: Joining Tables

BRAND --- BMW BMW BMW Audi Audi HA150 COLOR --- blue black green yellow orange 2014

HA150 Lesson: Data from Multiple Tables- Part 1

• Each row of the left table is connected to each row of the right table.

• There Is no CROSS JOIN join condition.

L x 1 2 3 4 5

Figure 187: CROSS JOIN

4 5 SELECT FROM Column, Column, Table, Table Column WHERE Condition;

SELECT Column, Column

FROM Table CROSS JOIN Table WHERE Condition;

Figure 188: CROSS JOIN

Each row of a table is connected to each row of the other table. J> The result set contains 10 • 20 = 200 rows

SELECT *

FROM Owner, car;

OWNER IO N� &Il>.THDAY CITY CAlll 0 PLATENUMUR eRAllD COLOR H�

--- --- --- ...

KOl Ms T 1934-06-20 Wl.esloc:h FOl HD-V 106 r1•t red

K02 Ms 0 1966-05-11 Kockenhe1m ro1 Ho-v 106 Fiat red 75

KOJ 51>.P AG ? Wa.l.ldorf ro1 HO•V 106 riat red 15

K01 1934-06-20 W.a.•tloeh F02 HD-VW 4'711 bl.ck 120

K02 ... 0 1966-0S-ll Kockenha:im F02 HO-VW ''711 "" black 120

K03 SAP AG ? Walldorf F02 HO-VW 4'111 black 120

KOl 1934-06-20 Wie•loc:b ro3 HO-JA 1972 - blue 184

H02 ... 0 1966-0S-ll Hocke.J'\haun F03 HO-J/\ 1912 - blue 184

H03 SAP >t; ? Wal.J.dort F03 HO-J'A 1972 - blue 184

1986-02-03 Ladeobu.rq 9raen 184 OWN KR H06 H06 H03 H03 H03 H03 HD3

Unit 4: Reading Data From Multiple Tables Part I HA150

Each row of a table is connected to each row of the other table.

:.-The result set contains 10 • 20 = 200 rows SELECT *

FROM owner CROSS JOrN car;

OWNER.IO N""°' BIRTHDAY CITY CAR ID PLATENUlCBER. BIWID COLOR HP CWNEQ

--- --- --- -_-----

H01 l<s T 1934-06-20 Wies ocl h F01 HD-V 106 Fiat red 806

H02 Ms u 1966-05- 11 Kockenhei.m FOl HO-V 106 Fiat red 75 H06

H_OJ SAP AG ? Wall.dorf FOl HO-V 106 Fiat red 15 H06

H01 _{Ms T} 1934-06-20 �11eeloc:h F02 HO-VW 4711 _blac:k 120 HOJ

H02 M• U 1966-CS-11 Hocke.nhei1t1 F02 HO-"v?1 4711 _"" blac:k 120 HOJ

HOJ SAP AG ? Wal1dorf F02 Ho •111 _"" black 120 H03 H₀₁ Ms T 1934-06-20 Wies loch E'03 HO-JA 1972 bl1.1e U4 HOJ

H02 Ms U 196_6-05- 11 Hockenheim FOJ HD-JA 1972 �· bl\le 184 HOJ

HOJ SAP AG ? �Jal.tdorf F03 HO-JA 1912 blue 184 H03 HlO 198_6•02·03 Ladenbur9 r2o ? green 184 ? Figure 190: Explicit CROSS JOIN

You can specify a WHERE clause for the Implicit and explicit CROSS JOIN.

SELECT *

FROM Owner, Car

WHERE HP > 250;

OWNERID NAME B lllTHllA't

--- --- --- HOl Mo T 1934-06-20 H02 Ms 0 1966·05-11 H03 SAP AG 9 H04 HllM AG ? HOS Mr V 1 9_S2-04-21 H06 Ms w 1957-06·01 H07 IICEA ? H08 Mr x 1986-08-30 H09 Ma y 1986-02-10 HlO Mr Z 1986-02-03

Figure 191: CROSS JOIN

CITY --- W.ie-.looh Kookenheim Wa.11.dorf Heidelberg Lei.men Wies-looh Wal1_dorf Walldorf Sinltheia Ladenburg SELECT *

FROM Owner CROSS JOrN _Car

WHERE HP > 250;

CAl\ID l't.ATENUMBER BRAND COLOR HP OWN&!\

---

F06 HD-VW 1999 Audi yell.QW 260 HOS

F06 HD-VW 1999 Audi yellow 260 HOS

F06 HD-VW 1999 Audi yellaw 260 HOS

F06 HD-VW 1999 _Alldi yellow 260 H_OS

F06 HD·VW 1999 Audi yel.low 260 ROS F06 !11>-VW 1999 _Audi yellow 260 HOS F06 llD·VW 1999 AwU yellow 260 aos

F06 HD-VW 1999 Audi yellow 260 KOS

F06 HD-VW l.999 Audi y&llow 260 KOS

F06 HD-VW 1999 Audi yellow 260 KOS

HA150 Lesson: Data from Multiple Tables- Part 1

You can join a table to itself.

, The result set contains 20 • 20 = 400 rows

SELECT *

FROM Owner, Car _FROM _Owner _CROSS _JOrN _Car

WHERE HP > 250· , _WHERE _HP _> _250;

c.\RID PUTENUMBJ!R BJWll) COLOR HP OWNER CAJUD PLAftNIMIER BJWll) COLOR HP OWIR

--- -·---··-·-

ro1 HD•V 106 Fi.at red 75 H06 rOl HD·V 10& , ... r<ld 75 H06

f'Ol HD-V 106 Fiat red 75 H06 F02 HD·'VW 4711 vw bl.aok 120 _R03

ro1 KD•V 106 r1at red 75 H06 ro3 HD•JA 1'?2 8l<W bl\I• 1 .. ROJ

HD•VW 47ll vw _black ₁₂₀ _H03 _ro1 _{HD•V 10&} riat. r<ld 75 H06

ro2 HD·VW 4111 vw black 120 803 ro2 HD·VW 4111 vw black 120 HOJ ro2 HD·VW 011 vw black 120 803 F03 HD·.IA 19?2 BMW blt.t• 184 803

ro3 llD·JA 1912 - blue 184 H03 ro1 HD·V 106 riot r<ld H06

ro3 HO•JA 1972 BMW blue 184 H03 ro2 HD·VW 4711 vw black 120 803

103 HD·JA 1'72 - bl.ue 184 803 ...03 HD·JA 1912 BMW blue 184 H03

F20 ? green 184 F20 ? Auch 184

Figure 192: CROSS JOIN

You can combine more than two tables.

>The result set contains 10 • 20 • 3 = 600 rows SELECT * FROM Owner, Car, Stolen; ltltTMDAT Cl'l'T ---- - --- ... ' 1934-06-20 Wies.loch IOI ... ' _ltl4-0C.-20 lfi••loch

IOI ... ' J.934-o&-20 Wiesloch

IOI ... ' _W4-0&-20 Wi•_•loch IOI ... ' 1'34-M-20 Wiesloch IOI M• t ltl4 ... 0C-20 Wi••loch

•02 M• U lt6i-05-11 Hookenh•i.a

M02 M• U 1,,,_0S-11 ffocUnh•ia

M02 M• U 1''6-05-11 ffoc;:k•nhei.9

MIO ... . l,86-oi-03 Ladenbui:9

Figure 193: CROSS JOIN

Figure 194: Joining Tables

CMlD fl.ATDWalA --- ID-V 101 POI e-v _10• SD-Y 101 11)--W ₄₇₁₁ llD-VM 4711 ro2 111>-VW ₄₁₁₁ MD-Y _JO' ro1 �y _10, MD-V 10fi ' INNER SELECT * Pi at Pi at "" vw vw r••t rt•t Pi•t.

FROM Owner CROSS JOIN

Car CROSS JOIN

Stolen; •UTUCIMUA UOllTP.ID_AT --- --- MX>-YW lff' _2012-CK-20 - ,, ""' NJ>..-y 10, _2:012-06-01 - ,, HC• KD-T "' _2012-os-21 bl•oll: ₁₂₀ H03 Ko--YW _1"9 ?O ₁₂""°'-20 bl•clt 120 H03 KD-Y _1°' _%012-06-01 bl•ck 120 "°' HD•Y 3.Jl _{2012•05 .. 21} - ₁₅ KO-Vlf _1ff' _2012-06-20 .... u _HOC Hl)•V ₁₀₆ _2'012-0,-0J - u _M06 HD-Y "' _2012-os-21 9l'••n 184 ' HD-Y "3 2012-05-21 JOIN

�

Unit 4: Reading Data From Multiple Tables Part I HA150

One row of the left table and one row of the right table are always joined to a common result row - provided that the JOIN condition is fulfilled.

> JOIN Condition: L. X = R. Y L x 1 2 3 4 5 ...

Figure 195: INNER JOIN

SELECT Column, Column, Column FROM Table, Table

R y 3 ... 5 ... 6 7 .. .

WHERE JOIN_Condition AND Supplementary_condition; SELECT Column, Column, Column

FROM Table JOIN Table ON JOIN Condition

WHERE Supplementary_Condition;

Figure 196: INNER JOIN

One row of a table and one row of another table are always connected to a common result row - provided that the JOIN condition is fulfilled.

•The JOIN condition is part of the WHERE clause

>To whom which car is registered?

SELECT *

FROM Owner, Car

WHERE OwnerID = Owner;

OllNERll) NAME BIR'll!DAr CITY CllRID J?LArENUMBER BRlUlD COLOR BP Ol'lllER

----_-- --- ---_---_-- --- --- ---

f!06 Ms w i957-06-0l Wiesloch _FOl _{BD-V 106} _Fi.at _red 75 806

B03 SJUI AG ? Walldorf' _F02 HD-vw nu vw blac:lc _.J.20 _B03

H03 SAP AG ? Wa1ldorf F03 HD-Jl>. 1972 _- blue 184 H03

H07 IKEA ? Walldorf' _F04 _{HD-AL 1002} _Mercedes _vlU.te _l.36 _R07 H03 SAP AG ? Wa1ldorf FOS BD�I 3206 Mercedes black 170 H03

Figure 197: Implicit INNER JOIN

HA150 _Lesson: _{Data from Multiple Tables- Part} 1

• One row of a table and one row of another table are always connected to a common result row - provided that the JOIN condition is fulfilled.

• The JOIN condition is part of the JOIN operation (and not of the WHERE clause) ;. To whom is which car registered?

SELECT *

FROM Owner INNER JOIN Car ON OwnerID - Owner;

OllNltlUD NAME BI�Y CITY CAAID PLATENUMBl!:R --- --- --- --- ---

H06 M.t " J.957-06-01 ffiea1och £01 HD•V 106 803 SAP AG ? Wal.l.dorf F02 HD-VW 4711 H03 SM' AG ? WaJ.ldorf F03 1m-JA 1972

807 IKEA ? WaJ.ldorf F04 HD-AL 1002

H03 SAP AG ? ffalldorf F05 lW•l!IM 3206

Figure 198: Explicit INNER JOIN

• You can simply write "JOIN" instead of "INNER JOIN".

8IU\l'ID COLOR RP ORNER --- Fiat red 75 H06 vw black: 120 H03 81."1 blue 184 803 Mercedes white 136 807 144rcodoa black: 170 803

• The INNER JOIN is the most important JOIN variant (and therefore the default)

;. To whom is which car registered?

SELECT *

FROM Owner JOIN Car ON _{OwnerID - Owner;}

OIJNEIUD ITTINE BntnlllM CrTT CARD> PLATDIUMBER BRAND COLOR RP OffNER

--- _-_--- _--- _---- _---_-_- _---

H06 ... " 1957-06-01 lfi .. loch l!'Ol HD·V 106 Fiat r4ld 75 H06

803 SAP AG ? Walldorf F02 HD-VW 4711 VII black: 120 H03

803 SAP AG ? Walldod l!'03 HD-JA 1972 - blue 184 803

807 IREA ? Walldod F04 HD-AL l.002 Mercedes white 136 807

803 SM AG ? ffalldorf: F05 HD-MM 3206 Mer code• black: 170 803

Figure 199: Explicit INNER JOIN

You can specify certain columns in the projection list also for the implicit

JOIN.

:; To whom is which car registered? � BRAND --- COLOR

Ho W Fiat red

SELECT Name, Brand, Color SAP AG SAP AG "" BMW b1ack blue

FROM Owner, Car IKEA SAP AG �reed.es Me reed.es black white

WHERE Owner ID - Owner; "" v SAP JIG Aueli Audi yeUow blue

Iia:A vw l>lack

HI U Skoda red

llDH AG BMW black

Ho U BMW i;reen

llDH AG Skoda red

IKEA Renau1t red

Unit 4: Reading Data From Multiple Tables Part I HA150

You can specify certain columns In the projection list also for the explicit JOIN.

l>To whom is which car registered? NllME BRAAtl _--- COLOR

Ms w fiat red

SELECT Name, Brand, Color SAP AG SAP AG BMW vw black blue

FROM Owner JOIN Car ll<XA _{SAP AG} Merced•• _Mercedes whJ.t• _b1ack

ON Owner ID Owner; Mr v _{SAP Al3} Aue11 _Aud.i y.llow _blue

Da:A vw black

Ms u Skoda red

llDM AG BMW black

M• U BMW green

llDM AG Skoda red

IKEA Renault red

SAP 1'G Mercedes white

SAP AG Skocla black

Ms Y Audi. orange

SAP AG Rena.ult rttd

Ms T vw black

Figure 201: Explicit INNER JOIN

You can also use table aliases or tuple variables in the projection list for the implicit JOIN.

+The JOIN condition can also refer to table aliases or tuple variables. +If column names are not unique, you must qualify them

with the table aliases or tuple variables. l>To whom is which car registered?

NAME M• W SAP AG SAP AG IKL� SAP AG BRANO COLOR --- Fiat red Vt< black BMW blue l1ercedes white l.fercedes black

SELECT o.Name, c.Brand, c.Color FROM Owner o, Car c

Mr V Audi yellow

SAP AG Audi

IKEA Vt<

WHERE o.OwnerID = c.Owner; >!S 0

Skoda KDM AG BMW ).fs U lll<W HOM AG Skoda Il<ZA Renault SAP M Merc:edes Sru? AG Skoda >!S y Audi SAP AG R•n•ult W.. T vw

Figure 202: Implicit INNER JOIN

You can use table aliases or tuple variables in the projection list for the explicit JOIN.

•The JOIN condition can also refer to table aliases or tuple variables. +If column names are not unique, you must qualify them

with the table aliases or tuple variables. ; To whom is which car registered?

SELECT o.Name, c.Brand, c.Color FROM Owner o JOIN Car c

ON o.OwnerID = c.Owner;

Figure 203: Explicit INNER JOIN

NAME SAP AG SAP AG Mr V SAP AG IKEA Ms 0 HOl& AG Ms 0 HOM AG IKEA SAP M SAP AG Ms Y SAP AG Ms T SRMO --- F;i.a.t Vt< BMW 1-lercedes l4ercede& Audi Vt< skoda BMW Bl«< Sko a Renault Merce�es skoda R9nault ..,.., blue black red black ��vn rod red whit.e black orange r•d bl.ac� COLOR rod bl.-c::k blue wbit.e black yellow blue bla.c::k red black i;;r.een red red white black orange rod black 2014

HA150 Lesson: Data from Multiple Tables- Part 1

You do not have to Include a column from every Involved table In the projection list

+Besides the JOIN condition the WHERE clause can contain additional conditions.

; To whom is a black car registered (at least one)?

SELECT DISTINCT o.Narne FROM Owner o, Car c

WHERE o.OwnerID = c.Owner

AND c.Color = 'black';

Figure 204: Implicit INNER JOIN

You do not need to include a column from every Involved table in the projection list.

+In addition to the JOIN condition you can specify a WHERE clause. :.. To whom is a black car registered (at least one)?

SELECT DISTINCT o.Name FROM Owner o JOIN Car c

ON o.OwnerID = c.owner

WHERE c.Color = 'black';

Figure 205: Explicit INNER JOIN

You can explicitly rename result columns for the Implicit JOIN.

+Sorting the result table is also possible

;. To whom is a black car registered (at least one)?

SELECT DISTINCT o.Narne AS "Owners' name"

FROM Owner o, Car c

WHERE o.OwnerID = c.Owner

AND c.Color = 'black'

Ms T SAP AG HOM AG IKEA NAME Ms T SAP AG HDM AG IKEA

ORDER BY o.Name; owners• name

Figure 206: Implicit INNER JOIN

HDM AG IKEA Ms T SAP AG

�

Unit 4: Reading Data From Multiple Tables Part I

You can explicitly rename result columns for the explicit JOIN.

+Sorting the result table is also possible

J> To whom is a black car registered (at least one)?

SELECT DISTINCT o.N_ame AS "Owners' n_ame" FROM Owner o JOIN Car c

ON o.OwnerID ₌ c.Owner

WHERE c.Color ₌ 1black1

ORDER _BY o.Name;

Figure 207: Explicit INNER JOIN

OWners · name

HOM AG IKEA Ms T SAP AG

You can add a GROUP BY clause for the implicit Join. J>How many black cars are registered to which owner?

SELECT o.Name, COUNT(*) AS _11# black cars" FROM Owner o, Car c

WHERE o.OwnerID = c.Owner AND c.Color =

'black'

GROUP _BY o.OwnerID, o.N_ame

ORDER _{BY 2} DESC, ₁ ASC;

Figure 208: Implicit INNER JOIN

NAME # black cars

SAP AG 3 HOM AG 1

IKEA 1

Ms T 1

You can add a GROUP BY clause for the explicit Join.

J>How many black cars are registered to which owner?

SELECT o.Name, COUNT(*) AS 11# black cars" FROM Owner o JOIN Car c

ON o.OwnerID ₌ c.Owner

WHERE c.Color ₌ 1black1

GROUP _BY o.OwnerID, o.Name

ORDER _{BY 2} DESC, ₁ ASC;

Figure 209: Explicit INNER JOIN

NAME # black cars

SAP AG 3 HOM AG 1 IKEA l Ms T l HA150 2014

HA150 Lesson: Data from Multiple Tables- Part 1

You can build the JOIN condition on multiple columns for the Implicit JOIN.

::;.. To whom within the EU is a black car registered (at least _one)?

SELECT DISTINCT o.Name AS "Owners' name"

FROM Owner_EU o, Car_EU c WHERE o.Country = c.Country

AND o.OwnerID = c.Owner

AND c.Color = 'black' ORDER BY o.Name;

Figure 210: Implicit INNER JOIN

Owners, name HDM AG IKEA Ms 0 Ms T SAP AG Senora _R

You can build the JOIN condition on multiple columns for the expl_{icit JOIN.} :;,. To whom within the EU is a black car registered (at least one)?

SEL.ECT DISTINCT o.Name AS "Owners' FROM Owner EU o JOIN Car EU c _- _-

ON o.Country = c.Country

AND o.OwnerID = c.Owner

WHERE c.Color = 'black'

ORDER BY o.Name;

Figure 211: Explicit INNER JOIN

You can join a table to itself.

;.Who is the manager of which employee?

name" Owners1 name HDM AG IKEA Ms 0 Ms T SAP AG Senora _R

SELECT e.Narne AS Employee, m.Name AS Manager FROM Official e, Official m

WHERE a.Manager = m.PNr; IEMPLOnl: Ml\NAGl!.R --- --- Mr A MS 0 Mr B Ms 0 Ms c Ms D

Unit 4: Reading Data From Multiple Tables Part I HA150

�

You can join a table to itself also for the explicit JOIN.

:.Who is the manager of which employee?

SELECT e.Name AS Employee, m.Name AS Manager FROM Official e JOIN Official m

ON e.Manager = m.PNr; E.MPLOll'EE MANAGER --- --- Hr A MS D Hr B MS D Ms c Ms I> Ms I> Mr I Mr E MS H l'lr F MS H Ms G Ms H Ms H M:r I Figure 213: Explicit INNER JOIN

You can combine more than two tables.

>To whom is which stolen car registered?

SELECT o.Name, a.Brand, a.Color, c.PlateNumber FROM Owner o, Car c, Stolen s

WHERE o.OwnerID = c.Owner

AND c.PlateNumber = s.PlateNumber;

NAME BRAND COLOR PLATENUMBER

--- --- --- Ms w Fiat red HD-V 106

Mr v Audi yellow HD-VW 1999

Ms y Audi orange HD-Y 333 Figure 214: Implicit INNER JOIN

You can combine more than two tables also for the explicit JOIN.

;. To whom is which stolen car registered?

SELECT o.Name, c.Brand, c.Color, c.PlateNurnber FROM owner o

JOIN Car c ON o.OwnerID = c.Owner

JOIN stolen s ON c.PlateNumber = s.PlateNurnber;

NAME BRAND COLOR PLATE NUMBER

--- --- ---

Ms w Fiat red HD-V 106

Mr v Audi yellow HD-VW 1999

Ms y Audi orange HD-Y 333

Figure 215: Explicit INNER JOIN

HA150 Lesson: Data from Multiple Tables- Part 1

You can use different comparison operators other than

EQUAL

in the JOIN condition.

)Which owner is older than which other owner?

SELECT o.Name AS "older", y.Name AS "younger"

FROM Owner o JOIN Owner y

ON o.Birthday < y.Birthday;

Figure 216: Explicit INNER JOIN

You can use calculations in the JOIN condition.

:i.Which car has at least three times the power than which othe_rcar?

SELECT m.*, l.* older younqer

In document Oficina Económica y Comercial de la Embajada de España en Shanghai. Estudios de Mercado. El sector de la Logística en China (página 75-79)