Types of Joins

1. Types of Joins • Farrokh Alemi, Ph.D.

2. Link Data across 2 or More Tables

4. Inner Join SELECTcolumn_name(s)FROM table1 INNERJOIN table2  ON table1.column_name = table2.column_name;

5. Inner Join Unique or Addressed SELECTcolumn_name(s)FROM table1 INNERJOIN table2  ON table1.column_name = table2.column_name;

6. Inner Join SELECTcolumn_name(s)FROM table1 INNERJOIN table2  ON table1.column_name = table2.column_name;

7. Inner Join • Inner Join SELECTcolumn_name(s)FROM table1 INNERJOIN table2  ON table1.column_name = table2.column_name;

8. Inner Join SELECTcolumn_name(s)FROM table1 INNERJOIN table2  ON table1.column_name = table2.column_name;

9. Inner Join

10. Inner Join

11. Inner Join SELECTc.*, d.*FROM [Dx Codes] d inner join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

12. Alias Alias Alias SELECTd.*, e.*FROM [Dx Codes] d inner join[Encounter] eONd.[Code ID] = e.[Diagnosis ID]

13. Inner Join SELECTd.*, e.*FROM [Dx Codes] d inner join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

14. Inner Join No Matching 6 SELECTd.*, e.*FROM [Dx Codes] d inner join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID] 6

15. Inner Join SELECTd.*, e.*FROM [Dx Codes] d inner join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID] Check Total Rows in Combined & Component Tables No Match Entire Record Gone POOF!

16. Cross Join • Left/Right Join All of Left Table Records Listed

17. Cross Join • Left/Right Join All of Right Table Records Listed

18. Cross Join • Left/Right Join SELECTd.*, e.*FROM [Dx Codes] d right join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

19. Cross Join • Left/Right Join SELECTd.*, e.*FROM [Dx Codes] d right join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

20. SELECTd.*, e.*FROM [Dx Codes] d right join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID] • Cross Join • Left/Right Join

21. Cross Join • Left/Right Join SELECTd.*, e.*FROM [Dx Codes] d left join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

22. Cross Join • Left/Right Join SELECTd.*, e.*FROM [Dx Codes] d left join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

23. SELECTd.*, e.*FROM [Dx Codes] d left join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID] • Cross Join • Left/Right Join

24. Cross Join • Full Join All Records Listed All Records Listed

25. Cross Join • Full Join SELECTd.*, e.*FROM [Dx Codes] d full join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

26. Cross Join • Full Join SELECTd.*, e.*FROM [Dx Codes] d full join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

27. Cross Join • Full Join SELECTd.*, e.*FROM [Dx Codes] d full join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID]

28. SELECTd.*, e.*FROM [Dx Codes] d full join [Encounter] e ONd.[Code ID] = e.[Diagnosis ID] • Cross Join • Full Join

29. Cross Join • Cross Join All Possible Combinations Without any Restrictions

30. Cross Join • Cross Join SELECTd.*, e.*FROM [Dx Codes] d cross join [Encounter] e ON d.[Code ID] = e.[Diagnosis ID]

31. SELECTd.*, e.*FROM [Dx Codes] d cross join [Encounter] e • Cross Join • Cross Join

32. SELECTd.*, e.*FROM [Dx Codes] d cross join [Encounter] e • Cross Join • Cross Join

33. SELECTd.*, e.*FROM [Dx Codes] d cross join [Encounter] e • Cross Join • Cross Join

34. Cross Join • Cross Join All Possible Combinations Lots of Data

35. Join command Connects two or More tables