All SQL Joins #db #tips

.gitignore

*.sqlite
*.db
*.log

sql-joins.sql

-- 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");