Create a gist now

Instantly share code, notes, and snippets.

@xiongjia /.gitignore
Last active Sep 14, 2017

What would you like to do?
All SQL Joins #db #tips
*.sqlite
*.db
*.log
-- DB Schema: SQL Joins
-- ( http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins )
--
-- > Sample of the test tables
-- TABLE_A TABLE_B
-- +----+------------+ +----+------------+
-- | PK | Value | | PK | Value |
-- +----+------------+ +----+------------+
-- | 1 | FOX | | 1 | TROT |
-- | 2 | COP | | 2 | CAR |
-- | 3 | TAXI | | 3 | CAB |
-- | 6 | WASHINGTON | | 6 | MONUMENT |
-- | 7 | DELL | | 7 | PC |
-- | 5 | ARIZONA | | 8 | MICROSOFT |
-- | 4 | LINCOLN | | 9 | APPLE |
-- | 10 | LUCENT | | 11 | SCOTCH |
-- +----+------------+ +----+------------+
--
-- > Import the schema to SQLite database:
-- `sqlite3 test_db.sqlite < sql-joins.sql`
--
-- > INNER JOIN
-- SELECT A.PK AS A_PK, A.Value AS A_Value,
-- B.Value AS B_Value, B.PK AS B_PK
-- FROM Table_A A
-- INNER JOIN Table_B B
-- ON A.PK = B.PK
--
-- > LEFT JOIN
-- SELECT A.PK AS A_PK, A.Value AS A_Value,
-- B.Value AS B_Value, B.PK AS B_PK
-- FROM Table_A A
-- LEFT JOIN Table_B B
-- ON A.PK = B.PK
--
-- > RIGHT JOIN (SQLite3 don't support it)
-- SELECT A.PK AS A_PK, A.Value AS A_Value,
-- B.Value AS B_Value, B.PK AS B_PK
-- FROM Table_A A
-- RIGHT JOIN Table_B B
-- ON A.PK = B.PK
--
-- > OUTER JOIN (SQLite3 don't support it)
-- SELECT A.PK AS A_PK, A.Value AS A_Value,
-- B.Value AS B_Value, B.PK AS B_PK
-- FROM Table_A A
-- FULL OUTER JOIN Table_B B
-- ON A.PK = B.PK
--
-- > LEFT EXCLUDING JOIN
-- SELECT A.PK AS A_PK, A.Value AS A_Value,
-- B.Value AS B_Value, B.PK AS B_PK
-- FROM Table_A A
-- LEFT JOIN Table_B B
-- ON A.PK = B.PK
-- WHERE B.PK IS NULL
--
-- > RIGHT EXCLUDING JOIN (SQLite3 don't support it)
-- SELECT A.PK AS A_PK, A.Value AS A_Value,
-- B.Value AS B_Value, B.PK AS B_PK
-- FROM Table_A A
-- RIGHT JOIN Table_B B
-- ON A.PK = B.PK
-- WHERE A.PK IS NULL
--
-- > OUTER EXCLUDING JOIN (SQLite3 don't support it)
-- SELECT A.PK AS A_PK, A.Value AS A_Value,
-- B.Value AS B_Value, B.PK AS B_PK
-- FROM Table_A A
-- FULL OUTER JOIN Table_B B
-- ON A.PK = B.PK
-- WHERE A.PK IS NULL OR B.PK IS NULL
--
-- Drop the test tables and create these tables again
DROP TABLE IF EXISTS TABLE_A;
DROP TABLE IF EXISTS TABLE_B;
CREATE TABLE TABLE_A (
PK INTEGER PRIMARY KEY,
Value TEXT NOT NULL
);
CREATE TABLE TABLE_B (
PK INTEGER PRIMARY KEY,
Value TEXT NOT NULL
);
-- Add test data
INSERT INTO TABLE_A ( PK, Value ) VALUES
( 1, "FOX"), ( 2, "COP"), ( 3, "TAXI"), ( 6, "WASHINGTON"),
( 7, "DELL"), ( 5, "ARIZONA"), ( 4, "LINCOLN"), (10, "LUCENT");
INSERT INTO TABLE_B ( PK, Value ) VALUES
( 1, "TROT"), ( 2, "CAR"), ( 3, "CAB"), ( 6, "MONUMENT"),
( 7, "PC"), ( 8, "MICROSOFT"), ( 9, "APPLE"), (11, "SCOTCH");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment