v Merge Tables - SQL

Merge Tables

Note: This tutorial was written using Catherine Devlin's SQL in Jupyter Notebooks library. If you have not using a Jupyter Notebook, you can ignore the two lines of code below and any line containing %%sql. Furthermore, this tutorial uses SQLite's flavor of SQL, your version might have some differences in syntax.

For more, check out Learning SQL by Alan Beaulieu.

# Ignore
%load_ext sql
%sql sqlite://
%config SqlMagic.feedback = False

Create Two Tables, Criminals And Crimes

%%sql

-- Create a table of criminals
CREATE TABLE criminals (pid, name, age, sex, city, minor);
INSERT INTO criminals VALUES (412, 'James Smith', 15, 'M', 'Santa Rosa', 1);
INSERT INTO criminals VALUES (234, 'Bill James', 22, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (632, 'Stacy Miller', 23, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (621, 'Betty Bob', NULL, 'F', 'Petaluma', 1);
INSERT INTO criminals VALUES (162, 'Jaden Ado', 49, 'M', NULL, 0);
INSERT INTO criminals VALUES (901, 'Gordon Ado', 32, 'F', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (512, 'Bill Byson', 21, 'M', 'Santa Rosa', 0);
INSERT INTO criminals VALUES (411, 'Bob Iton', NULL, 'M', 'San Francisco', 0);

-- Create a table of crimes
CREATE TABLE crimes (cid, crime, city, pid_arrested, cash_stolen);
INSERT INTO crimes VALUES (1, 'fraud', 'Santa Rosa', 412, 40000);
INSERT INTO crimes VALUES (2, 'burglary', 'Petaluma', 234, 2000);
INSERT INTO crimes VALUES (3, 'burglary', 'Santa Rosa', 632, 2000);
INSERT INTO crimes VALUES (4, NULL, NULL, 621, 3500); 
INSERT INTO crimes VALUES (5, 'burglary', 'Santa Rosa', 162, 1000); 
INSERT INTO crimes VALUES (6, NULL, 'Petaluma', 901, 50000); 
INSERT INTO crimes VALUES (7, 'fraud', 'San Francisco', 412, 60000); 
INSERT INTO crimes VALUES (8, 'burglary', 'Santa Rosa', 512, 7000); 
INSERT INTO crimes VALUES (9, 'burglary', 'San Francisco', 411, 3000); 
INSERT INTO crimes VALUES (10, 'robbery', 'Santa Rosa', 632, 2500); 
INSERT INTO crimes VALUES (11, 'robbery', 'Santa Rosa', 512, 3000);
[]

Inner Join

Returns all rows whose merge-on id appears in both tables.

%%sql

-- Select everything
SELECT *

-- Left table
FROM criminals

-- Right table
INNER JOIN crimes

-- Merged on `pid` in the criminals table and `pid_arrested` in the crimes table
ON criminals.pid=crimes.pid_arrested;
pid name age sex city minor cid crime city_1 pid_arrested cash_stolen
412 James Smith 15 M Santa Rosa 1 1 fraud Santa Rosa 412 40000
412 James Smith 15 M Santa Rosa 1 7 fraud San Francisco 412 60000
234 Bill James 22 M Santa Rosa 0 2 burglary Petaluma 234 2000
632 Stacy Miller 23 F Santa Rosa 0 3 burglary Santa Rosa 632 2000
632 Stacy Miller 23 F Santa Rosa 0 10 robbery Santa Rosa 632 2500
621 Betty Bob None F Petaluma 1 4 None None 621 3500
162 Jaden Ado 49 M None 0 5 burglary Santa Rosa 162 1000
901 Gordon Ado 32 F Santa Rosa 0 6 None Petaluma 901 50000
512 Bill Byson 21 M Santa Rosa 0 8 burglary Santa Rosa 512 7000
512 Bill Byson 21 M Santa Rosa 0 11 robbery Santa Rosa 512 3000
411 Bob Iton None M San Francisco 0 9 burglary San Francisco 411 3000

Left Join

Returns all rows from the left table but only the rows from the right left that match the left table.

%%sql

-- Select everything
SELECT *

-- Left table
FROM criminals

-- Right table
LEFT JOIN crimes

-- Merged on `pid` in the criminals table and `pid_arrested` in the crimes table
ON criminals.pid=crimes.pid_arrested;
pid name age sex city minor cid crime city_1 pid_arrested cash_stolen
412 James Smith 15 M Santa Rosa 1 1 fraud Santa Rosa 412 40000
412 James Smith 15 M Santa Rosa 1 7 fraud San Francisco 412 60000
234 Bill James 22 M Santa Rosa 0 2 burglary Petaluma 234 2000
632 Stacy Miller 23 F Santa Rosa 0 3 burglary Santa Rosa 632 2000
632 Stacy Miller 23 F Santa Rosa 0 10 robbery Santa Rosa 632 2500
621 Betty Bob None F Petaluma 1 4 None None 621 3500
162 Jaden Ado 49 M None 0 5 burglary Santa Rosa 162 1000
901 Gordon Ado 32 F Santa Rosa 0 6 None Petaluma 901 50000
512 Bill Byson 21 M Santa Rosa 0 8 burglary Santa Rosa 512 7000
512 Bill Byson 21 M Santa Rosa 0 11 robbery Santa Rosa 512 3000
411 Bob Iton None M San Francisco 0 9 burglary San Francisco 411 3000

Note: FULL OUTER and RIGHT JOIN are not shown here because they are not supported by the version of SQL (SQLite) used in this tutorial.