21 types of sql joins
0 likes382 views
This document discusses different types of joins in TSQL including inner joins, left outer joins, full outer joins, right outer joins, cross joins, semi joins, anti semi joins, non-equi joins, cross apply, outer apply, union, intersect, and except. It provides code examples of each type of join and describes how they relate two or more tables.
![Created By Steve Stedman http://SteveStedman.com
Twitter @SqlEmt http://linkedin.com/in/stevestedman
TSQL JOIN TYPES
Created by Steve Stedman
Two FULL OUTER JOINS
SELECT *
FROM Table1 t1
FULL OUTER JOIN Table2 t2
ON t1.fk = t2.id
FULL OUTER JOIN Table3 t3
ON t1.fk_table3 = t3.id;
Table1
Table3
Table2
Two LEFT OUTER JOINS
SELECT *
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.fk = t2.id
LEFT OUTER JOIN Table3 t3
ON t1.fk_table3 = t3.id;
Table1
Table3
Table2
INNER JOIN and a LEFT OUTER JOIN
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.fk = t2.id
LEFT OUTER JOIN Table3 t3
ON t1.fk_table3 = t3.id;
Table1
Table3
Table2
Two INNER JOINs
SELECT *
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.fk = t2.id
INNER JOIN Table3 t3
ON t1.fk_table3 = t3.id;
Table1
Table3
Table2
SELECT *
FROM Table1 t1
CROSS APPLY
[dbo].[someTVF](t1.fk)
AS t;
CROSS APPLY
Correlated
Sub-query
or Table
Valued
Function
Table1
SELECT *
FROM Table1 t1
OUTER APPLY
[dbo].[someTVF](t1.fk)
AS t;
OUTER APPLY
Correlated
Sub-query
or Table
Valued
Function
Table1
SELECT fk as id
FROM Table1
EXCEPT
SELECT ID
FROM Table2;
EXCEPT
Table2Table1
SELECT fk as id
FROM Table1
INTERSECT
SELECT ID
FROM Table2;
INTERSECT
Table2Table1
SELECT fk as id
FROM Table1
UNION
SELECT ID
FROM Table2;
UNION
Table2Table1
Table 1
(People)
Table 2
(Favorite Colors)
Table 3
(Favorite Foods)
Sample Schema
Note: Column names are very
generic to simplify the sample
queries.
Foreign keys are
Table1.fk -> Table2.id
Table2.fk_table3 -> Table3.id
Page 2 of 2](https://image.slidesharecdn.com/21typesofsqljoins-200325185130/85/21-types-of-sql-joins-2-320.jpg)
21 types of sql joins
- 1. Created By Steve Stedman http://SteveStedman.com Twitter @SqlEmt http://linkedin.com/in/stevestedman TSQL JOIN TYPES Created by Steve Stedman SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id; LEFT OUTER JOIN Table1 Table2 INNER JOIN SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.fk = t2.id; Table2Table1 FULL OUTER JOIN SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.fk = t2.id; Table2Table1 SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id WHERE t2.id IS NULL; LEFT OUTER JOIN with exclusion replacement for a NOT IN Table2Table1 SELECT * FROM Table1; SELECT * FROM Table2; SELECT from two tables Table1 Table2 SEMI JOIN SELECT * FROM Table1 t1 WHERE EXISTS (SELECT 1 FROM Table2 t2 WHERE t1.fk = t2.id ); Table2Table1 SELECT * FROM Table1 t1 WHERE NOT EXISTS (SELECT 1 FROM Table2 t2 WHERE t1.fk = t2.id ); ANTI SEMI JOIN Table2Table1 SELECT * FROM Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.fk = t2.id WHERE t1.fk IS NULL; RIGHT OUTER JOIN with exclusion replacement for a NOT IN Table2Table1 RIGHT OUTER JOIN SELECT * FROM Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.fk = t2.id; Table2Table1 SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.fk = t2.id WHERE t1.fk IS NULL OR t2.id IS NULL; FULL OUTER JOIN with exclusion Table2Table1 NON-EQUI INNER JOIN SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.fk >= t2.id; Table2Table1 SELECT * FROM Table1 t1 CROSS JOIN Table2 t2; CROSS JOIN, the Cartesian product Table2Table1 Page 1 of 2
- 2. Created By Steve Stedman http://SteveStedman.com Twitter @SqlEmt http://linkedin.com/in/stevestedman TSQL JOIN TYPES Created by Steve Stedman Two FULL OUTER JOINS SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.fk = t2.id FULL OUTER JOIN Table3 t3 ON t1.fk_table3 = t3.id; Table1 Table3 Table2 Two LEFT OUTER JOINS SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.fk = t2.id LEFT OUTER JOIN Table3 t3 ON t1.fk_table3 = t3.id; Table1 Table3 Table2 INNER JOIN and a LEFT OUTER JOIN SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.fk = t2.id LEFT OUTER JOIN Table3 t3 ON t1.fk_table3 = t3.id; Table1 Table3 Table2 Two INNER JOINs SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.fk = t2.id INNER JOIN Table3 t3 ON t1.fk_table3 = t3.id; Table1 Table3 Table2 SELECT * FROM Table1 t1 CROSS APPLY [dbo].[someTVF](t1.fk) AS t; CROSS APPLY Correlated Sub-query or Table Valued Function Table1 SELECT * FROM Table1 t1 OUTER APPLY [dbo].[someTVF](t1.fk) AS t; OUTER APPLY Correlated Sub-query or Table Valued Function Table1 SELECT fk as id FROM Table1 EXCEPT SELECT ID FROM Table2; EXCEPT Table2Table1 SELECT fk as id FROM Table1 INTERSECT SELECT ID FROM Table2; INTERSECT Table2Table1 SELECT fk as id FROM Table1 UNION SELECT ID FROM Table2; UNION Table2Table1 Table 1 (People) Table 2 (Favorite Colors) Table 3 (Favorite Foods) Sample Schema Note: Column names are very generic to simplify the sample queries. Foreign keys are Table1.fk -> Table2.id Table2.fk_table3 -> Table3.id Page 2 of 2
- 3. Created By Steve Stedman http://SteveStedman.com Twitter @SqlEmt http://linkedin.com/in/stevestedman TSQL JOIN TYPES Created by Steve Stedman Enjoying my free JOIN Types poster. Take a look at another freebie available at http://DatabaseHealth.com . A free SQL Server performance monitoring tool that I have made available to the SQL Server Community. Free download, free to use, no strings attached. Enjoy!