Setup Environment:
CREATE TABLE PERSON
(
NAME VARCHAR2(255),
SURNAME VARCHAR2(255),
CITY VARCHAR2(255),
COUNTRYCODE VARCHAR2(255)
)
CREATE TABLE COUNTRY
(
ISOCODE VARCHAR2(2),
NAME_UK VARCHAR2(255),
NAME_DE VARCHAR2(255)
)
INSERT INTO PERSON (NAME, SURNAME, CITY, COUNTRYCODE) VALUES ('Woll', 'Felicitas', 'Berlin', 'DE')
INSERT INTO PERSON (NAME, SURNAME, CITY, COUNTRYCODE) VALUES ('Cotillard', 'Marion', 'Paris', 'FR')
INSERT INTO PERSON (NAME, SURNAME, CITY, COUNTRYCODE) VALUES ('Bond', 'James', 'London', 'UK')
INSERT INTO COUNTRY (ISOCODE, NAME_UK, NAME_DE) VALUES ('DE', 'Germany', 'Deutschland')
INSERT INTO COUNTRY (ISOCODE, NAME_UK, NAME_DE) VALUES ('FR', 'France', 'Frankreich')
INSERT INTO COUNTRY (ISOCODE, NAME_UK, NAME_DE) VALUES ('IT', 'Italy', 'Italien')
COMMIT
Show Table content:
SELECT * FROM PERSON
NAME | SURNAME | CITY | COUNTRYCODE | Woll | Felicitas | Berlin | DE | Cotillard | Marion | Paris | FR | Bond | James | London | UK |
---|
SELECT * FROM COUNTRY
ISOCODE | NAME_UK | NAME_DE | DE | Germany | Deutschland | FR | France | Frankreich | IT | Italy | Italien |
---|
Inner Join:
SELECT p.NAME, p.COUNTRYCODE,
c.ISOCODE, c.NAME_UK, c.NAME_DE
FROM PERSON p
INNER JOIN COUNTRY c ON (c.ISOCODE = p.COUNTRYCODE)
ORDER BY p.NAME, c.NAME_UK
NAME | COUNTRYCODE | ISOCODE | NAME_UK | NAME_DE | Cotillard | FR | FR | France | Frankreich | Woll | DE | DE | Germany | Deutschland |
---|
Left Outer Join:
SELECT p.NAME, p.COUNTRYCODE,
c.ISOCODE, c.NAME_UK, c.NAME_DE
FROM PERSON p
LEFT OUTER JOIN COUNTRY c ON (c.ISOCODE = p.COUNTRYCODE)
ORDER BY p.NAME, c.NAME_UK
NAME | COUNTRYCODE | ISOCODE | NAME_UK | NAME_DE | Bond | UK | - | - | - | Cotillard | FR | FR | France | Frankreich | Woll | DE | DE | Germany | Deutschland |
---|
Right Outer Join:
SELECT p.NAME, p.COUNTRYCODE,
c.ISOCODE, c.NAME_UK, c.NAME_DE
FROM PERSON p
RIGHT OUTER JOIN COUNTRY c ON (c.ISOCODE = p.COUNTRYCODE)
ORDER BY p.NAME, c.NAME_UK
NAME | COUNTRYCODE | ISOCODE | NAME_UK | NAME_DE | Cotillard | FR | FR | France | Frankreich | Woll | DE | DE | Germany | Deutschland | - | - | IT | Italy | Italien |
---|
Full Join:
SELECT p.NAME, p.COUNTRYCODE,
c.ISOCODE, c.NAME_UK, c.NAME_DE
FROM PERSON p
FULL OUTER JOIN COUNTRY c ON (c.ISOCODE = p.COUNTRYCODE)
ORDER BY p.NAME, c.NAME_UK
NAME | COUNTRYCODE | ISOCODE | NAME_UK | NAME_DE | Bond | UK | - | - | - | Cotillard | FR | FR | France | Frankreich | Woll | DE | DE | Germany | Deutschland | - | - | IT | Italy | Italien |
---|
Cross Join:
SELECT p.NAME, p.COUNTRYCODE,
c.ISOCODE, c.NAME_UK, c.NAME_DE
FROM PERSON p
CROSS JOIN COUNTRY c
ORDER BY p.NAME, c.NAME_UK
NAME | COUNTRYCODE | ISOCODE | NAME_UK | NAME_DE | Bond | UK | FR | France | Frankreich | Bond | UK | DE | Germany | Deutschland | Bond | UK | IT | Italy | Italien | Cotillard | FR | FR | France | Frankreich | Cotillard | FR | DE | Germany | Deutschland | Cotillard | FR | IT | Italy | Italien | Woll | DE | FR | France | Frankreich | Woll | DE | DE | Germany | Deutschland | Woll | DE | IT | Italy | Italien |
---|