Learning machine learning? Try my machine learning flashcards or Machine Learning with Python Cookbook.
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 |