lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle SQL Joins Quick Start


Quick Start contains a summary of all the SQL from this section. Cut and paste this script into a command line client such as SQL Developer or TOAD to experiment with the SQL syntax.
-- Drop the tables if they already exist
DROP TABLE visit;
DROP TABLE head_of_state;
DROP TABLE city;
DROP TABLE country;
DROP TABLE continent;

-- Create and populate the CONTINENT table
CREATE TABLE continent (
  cont_id    NUMBER,
  cont_name  VARCHAR2(20)
);

INSERT INTO continent (cont_id, cont_name) VALUES (1, 'Africa');
INSERT INTO continent (cont_id, cont_name) VALUES (2, 'Asia');
INSERT INTO continent (cont_id, cont_name) VALUES (3, 'Europe');
INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North America');
INSERT INTO continent (cont_id, cont_name) VALUES (5, 'South America');
INSERT INTO continent (cont_id, cont_name) VALUES (6, 'Oceania');
INSERT INTO continent (cont_id, cont_name) VALUES (7, 'Antarctica');
COMMIT;

-- Create and populate the COUNTRY table
CREATE TABLE country (
  ctry_id         NUMBER,
  cont_id         NUMBER,
  ctry_name       VARCHAR2(50),
  ctry_area       NUMBER,
  ctry_pop        NUMBER,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)
);

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(48, 1, 'Ghana', 92098, 23837000, null, 'Cedi');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(53, 6, 'Australia', 2966200, 21884000, '4-sep-09', 'Australian Dollar');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(73, 3, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(122, 3, 'Georgia', 26900, 4382100, '1-jan-09', 'Lari');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(123, 6, 'New Zealand', 104454, 4320300, '4-sep-09', 'New Zealand Dollar');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(147, 1, 'Gambia', 4361, 1705000, null, 'Dalasi');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(149, 1, 'Gabon', 103347, 1475000, null, 'CFA franc');
COMMIT;

-- Create and populate the CITY table
CREATE TABLE city (
  city_id     NUMBER,
  ctry_id     NUMBER,
  city_name   VARCHAR2(50),
  city_area   NUMBER,
  city_pop    NUMBER
);

INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (1, 53, 'Sydney', 4689, 4399722);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (2,	53, 'Melbourne', 3400, 3892419);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (3,	53, 'Brisbane', 2279, 1945639);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (4,	53, 'Perth', 2079, 1602559);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (5,	53, 'Adelaide', 705, 1172105);

INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (6,	123, 'Auckland', 419, 1313200);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (7,	123, 'Christchurch', 550, 368900);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (8,	123, 'Wellington', 171, 381900);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (9,	123, 'Hamilton', 98, 138500);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (10, 123, 'Tauranga', 64, 110500);

INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (11, 73, 'Athens', 15, 745514);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (12, 73, 'Thessaloniki', 7 , 763468);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (13, 73, 'Piraeus', 4, 175697);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (14, 73, 'Patras', 48, 171616);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (15, 73, 'Heraklion', 42, 137711);

INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (16, 14, 'Berlin', 344, 3396300);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (17, 14, 'Hamburg', 291, 1731200);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (18, 14, 'Munich', 119, 1241100);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (19, 14, 'Cologne', 156, 969500);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (20, 14, 'Frankfurt', 95, 646000);

INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (21, 122, 'Tbilisi', 280, 1382900);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (22, 122, 'Kutaisi', 27, 267300);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (23, 122, 'Batumi', null, 144600);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (24, 122, 'Rustavi', 23, 121786);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (25, 122, 'Zugdidi', null, 104900);
COMMIT;

-- Create and populate the HEAD_OF_STATE table
CREATE TABLE head_of_state (
  hos_id              NUMBER,
  ctry_id             NUMBER,
  hos_name            VARCHAR2(50)
);

INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
  VALUES (1, 122, 'President Mikheil Saakashvili');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
  VALUES (2, 14, 'President Horst Köhler');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
  VALUES (3, 73, 'President Karolos Papoulias');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
  VALUES (4, 48, 'President John Atta Mills');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
  VALUES (5, 147, 'President Yahya Jammeh');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
  VALUES (6, 149, 'President-elect Ali Bongo Ondimba');
COMMIT;

-- Create and populate the VISIT table
CREATE TABLE visit (
  ctry_id           NUMBER,
  hos_id            NUMBER
);

INSERT INTO visit (ctry_id, hos_id) VALUES (14, 3);
INSERT INTO visit (ctry_id, hos_id) VALUES (48, 3);
INSERT INTO visit (ctry_id, hos_id) VALUES (149, 3);
INSERT INTO visit (ctry_id, hos_id) VALUES (73, 2);
INSERT INTO visit (ctry_id, hos_id) VALUES (73, 1);
COMMIT;


------- Inner Join -------

-- Implicit inner join returning a single row.
-- The continent that Greece is in.
SELECT cont_name
FROM country
  JOIN continent USING (cont_id)
WHERE ctry_name = 'Greece';

-- Implicit inner join returning multiple rows.
-- Countries in Europe.
SELECT ctry_name
FROM country
  JOIN continent USING (cont_id)
WHERE cont_name = 'Europe';

-- Multiple inner joins.
-- Cities in Oceania.
SELECT city_name, ctry_name
FROM continent
  JOIN country USING (cont_id)
  JOIN city USING (ctry_id)
WHERE cont_name = 'Oceania'
ORDER BY city_name;

-- Explicit inner join.
-- Continents and their countries.
SELECT cont_name, ctry_name
FROM continent
  INNER JOIN country USING (cont_id);

-- Join using ON.
-- Cities which are larger than countries.
SELECT city_name, city_area, ctry_name, ctry_area
FROM country
  JOIN city ON city_area > ctry_area;

-- Multiple join conditions using ON.
-- Cities with a population greater than 10% of the entire population.
SELECT city_name, city_pop, ctry_name, ctry_pop
FROM country
  JOIN city ON city_pop > ctry_pop * 0.1
            AND country.ctry_id = city.ctry_id;

-- Complex join.
-- Cities in countries that do not use the Euro as their currency,
-- that have a population greater than one percent of the country total
-- and have less than or the same number of letters in the name
-- as the continent they are in.
SELECT city_name, ctry_name, cont_name
FROM continent
  JOIN country ctry USING (cont_id)
  JOIN city ON city_pop > ctry_pop * .01
            AND ctry.ctry_id = city.ctry_id
            AND length(city_name) <= length(cont_name)
WHERE currency != 'Euro';


------- Outer Join -------

-- Left outer join.
-- All continents and their countries.
SELECT cont_name, ctry_name
FROM continent
 LEFT OUTER JOIN country USING (cont_id);

-- Multiple left outer joins.
-- All countries and their heads of state.
SELECT ctry_name, hos_name
FROM country
  LEFT OUTER JOIN visit USING (ctry_id)
  LEFT OUTER JOIN head_of_state USING (hos_id);

-- Multiple right outer joins.
-- All heads of state and their countries.
SELECT ctry_name, hos_name
FROM country
  RIGHT OUTER JOIN visit USING (ctry_id)
  RIGHT OUTER JOIN head_of_state USING (hos_id);

-- Full outer join
-- All countries and all heads of state.
SELECT ctry_name, hos_name
FROM country
  FULL OUTER JOIN visit USING (ctry_id)
  FULL OUTER JOIN head_of_state USING (hos_id);

-- Left outer join with extra selection criteria.
-- Countries and their heads of state excluding President Köhler
SELECT ctry_name, hos_name
FROM country
  LEFT OUTER JOIN visit USING (ctry_id)
  LEFT OUTER JOIN head_of_state USING (hos_id)
WHERE hos_name != 'President Horst Köhler';

-- Left outer join with extra selection criteria.
-- All countries and their heads of state excluding President Köhler.
SELECT ctry_name, hos_name
FROM country
  LEFT OUTER JOIN visit USING (ctry_id)
  LEFT OUTER JOIN head_of_state USING (hos_id)
WHERE hos_name != 'President Horst Köhler'
  OR hos_name IS NULL;


------- Other Join Types -------

-- Self join.
-- Countries larger than or equal to the size of New Zealand.
SELECT ctry.ctry_name, ctry.ctry_area, nz.ctry_area AS nz_area
FROM country nz
  JOIN country ctry ON ctry.ctry_area >= nz.ctry_area
                    AND ctry.ctry_id != nz.ctry_id
WHERE nz.ctry_name = 'New Zealand';

-- Cross join.
-- All heads of state and the continents they may potentially visit.
SELECT hos_name, cont_name, ' ' AS "VISITING (PLEASE TICK)"
FROM continent
  CROSS JOIN head_of_state
ORDER BY hos_name, cont_name;

-- Theta-stye join.
-- Countries and their continents.
SELECT ctry_name, cont_name
FROM continent cont, country ctry
WHERE cont.cont_id = ctry.cont_id;

-- Theta-style join.
-- Cities larger than 10,000 sq miles in Oceania.
SELECT ctry_name, city_name
FROM continent cont, country ctry, city
WHERE cont.cont_id = ctry.cont_id
  AND ctry.ctry_id = city.ctry_id
  AND ctry_area > 10000
  AND cont_name = 'Oceania';

-- Same query as above using ANSI join syntax.
-- Cities larger than 10,000 sq miles in Oceania.
SELECT ctry_name, city_name
FROM continent
  JOIN country USING (cont_id)
  JOIN city USING (ctry_id)
WHERE ctry_area > 10000
  AND cont_name = 'Oceania';

-- Natural join.
-- City areas in Greece.
SELECT city_name, city_area
FROM country
  NATURAL JOIN city
WHERE ctry_name='Greece';
»