Left Join

Create Table Of Superheroes

-- Create a table called SUPERHEROES. If it already exists, replace it.
CREATE OR REPLACE TABLE SUPERHEROES (
  -- Column called ID allowing up to five characters
  "ID" VARCHAR(5), 
  -- Column called NAME allowing up to 100 characters
  "NAME" VARCHAR(100),
  -- Column called ALTER_EGO allowing up to 100 characters
  "ALTER_EGO" VARCHAR(100),
  -- Column called BANK_BALANCE allowing 38 digits with 2 after the decimal point
  "BANK_BALANCE" NUMBER(38, 2)
);

Insert Rows For Each Superhero

-- Insert rows into SUPERHEROES
INSERT INTO SUPERHEROES 
    -- With the values
    VALUES
    ('XF6K4', 'Chris Maki', 'The Bomber', '-100.20'),
    ('KD5SK', 'Donny Mav', 'Nuke Miner', '200.30');

View Table Of Superheroes

-- View the table
SELECT * FROM SUPERHEROES;
ID NAME ALTER_EGO BANK_BALANCE
XF6K4 Chris Maki The Bomber -100.20
KD5SK Donny Mav Nuke Miner 200.30

Create Table Of Superhero Powers

-- Create a table called POWERS. If it already exists, replace it.
CREATE OR REPLACE TABLE POWERS (
  -- Column called ID allowing up to five characters
  "ID" VARCHAR(5), 
  -- Column called SUPER_POWER allowing up to 100 characters
  "SUPER_POWER" VARCHAR(100)
);

Insert Rows For Each Superpower

-- Insert rows into POWERS
INSERT INTO POWERS 
    -- With the values
    VALUES
    ('XF6K4', 'invisibility'),
    ('KD5SK', 'fire blast'),
    ('TKSI1', 'mind control') -- Note that this ID doesn't appear in the SUPERHEROES table
;

Merge Superhero And Superpower Tables

Notice that after this merge there are two ID columns. This is because both tables contained an ID column and therefore they both will appear in the final table.

-- Select all columns
SELECT * 
-- From SUPERHEROES table
FROM SUPERHEROES 
-- After left joining in the POWERS table
LEFT JOIN POWERS
-- Where the ID's in both tables are the same
ON SUPERHEROES.ID = POWERS.ID
;
ID NAME ALTER_EGO BANK_BALANCE ID SUPER_POWER
XF6K4 Chris Maki The Bomber -100.20 XF6K4 invisibility
KD5SK Donny Mav Nuke Miner 200.30 KD5SK fire blast