-- SQL Code to illustrate excellent, venn diagram, teaching aid
-- on SQL join types https://i.stack.imgur.com/UI25E.jpg
CREATE TABLE #Dude ( DudeID INTEGER , DudeName VARCHAR(20) , JobID INTEGER ) CREATE TABLE #Jobs ( JobID INTEGER , JobName VARCHAR(20) ) -- Dummy Data INSERT INTO #Jobs VALUES (1, 'Boss') INSERT INTO #Jobs VALUES (2, 'Manager') INSERT INTO #Jobs VALUES (3, 'Employee') INSERT INTO #Jobs VALUES (5, 'Slave') INSERT INTO #Dude VALUES (1, 'Alice' , 1) INSERT INTO #Dude VALUES (2, 'Bob' , 2) INSERT INTO #Dude VALUES (3, 'Charlie' , 3) INSERT INTO #Dude VALUES (4, 'Dave' , 4) INSERT INTO #Dude VALUES (5, 'Erin' , 1) INSERT INTO #Dude VALUES (6, 'Frank' , 2) INSERT INTO #Dude VALUES (7, 'Gertrude' , 3) INSERT INTO #Dude VALUES (8, 'Heath' , 4) INSERT INTO #Dude VALUES (9, 'Irene' , 4) --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- INNER JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D INNER JOIN #Jobs J On J.JobID = D.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- LEFT JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D LEFT JOIN #Jobs J On J.JobID = D.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- RIGHT JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D RIGHT JOIN #Jobs J On J.JobID = D.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- FULL OUTER JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D FULL OUTER JOIN #Jobs J ON D.JobID = J.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- LEFT JOIN - with null check SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D LEFT JOIN #Jobs J On J.JobID = D.JobID WHERE J.JobID IS NULL ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- RIGHT JOIN - with null check SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D RIGHT JOIN #Jobs J On J.JobID = D.JobID WHERE D.DudeID IS NULL ORDER BY D.DudeID -- Tidy Up DROP TABLE #Dude DROP TABLE #Jobs
CREATE TABLE #Dude ( DudeID INTEGER , DudeName VARCHAR(20) , JobID INTEGER ) CREATE TABLE #Jobs ( JobID INTEGER , JobName VARCHAR(20) ) -- Dummy Data INSERT INTO #Jobs VALUES (1, 'Boss') INSERT INTO #Jobs VALUES (2, 'Manager') INSERT INTO #Jobs VALUES (3, 'Employee') INSERT INTO #Jobs VALUES (5, 'Slave') INSERT INTO #Dude VALUES (1, 'Alice' , 1) INSERT INTO #Dude VALUES (2, 'Bob' , 2) INSERT INTO #Dude VALUES (3, 'Charlie' , 3) INSERT INTO #Dude VALUES (4, 'Dave' , 4) INSERT INTO #Dude VALUES (5, 'Erin' , 1) INSERT INTO #Dude VALUES (6, 'Frank' , 2) INSERT INTO #Dude VALUES (7, 'Gertrude' , 3) INSERT INTO #Dude VALUES (8, 'Heath' , 4) INSERT INTO #Dude VALUES (9, 'Irene' , 4) --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- INNER JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D INNER JOIN #Jobs J On J.JobID = D.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- LEFT JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D LEFT JOIN #Jobs J On J.JobID = D.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- RIGHT JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D RIGHT JOIN #Jobs J On J.JobID = D.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- FULL OUTER JOIN SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D FULL OUTER JOIN #Jobs J ON D.JobID = J.JobID ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- LEFT JOIN - with null check SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D LEFT JOIN #Jobs J On J.JobID = D.JobID WHERE J.JobID IS NULL ORDER BY D.DudeID --==--==--==--==--==--==--==--==--==--==--==--==--==--== -- RIGHT JOIN - with null check SELECT D.DudeID, D.DudeName, D.JobID, J.JobID, J.JobName FROM #Dude D RIGHT JOIN #Jobs J On J.JobID = D.JobID WHERE D.DudeID IS NULL ORDER BY D.DudeID -- Tidy Up DROP TABLE #Dude DROP TABLE #Jobs
