SQL Join Types Created by
SELECT *
Table 1 Table 2 FROM Table_1;
1 SELECT
FROM
*
Table_2;
Select from two tables
SELECT *
Table 1 Table 2
FROM Table_1 t1
2 INNER
ON [Link]
JOIN
=
Table_2
[Link]
t2
INNER JOIN
SELECT *
Table 1 Table 2
FROM Table_1 t1
3 LEFT
ON
JOIN
[Link]
Tbale_2
= [Link];
t2
LEFT OUTER JOIN
SQL Join Types Created by
SELECT *
Table 1 Table 2 FROM Table_1 t1
4
RIGHT JOIN Tbale_2 t2
ON [Link] = [Link];
RIGHT OUTER JOIN
SELECT *
FROM Table_1 t1
Table 1 Table 2 WHERE EXISTS (SELECT 1
FROM Table_2 t2
5 SEMI JOIN — Similar to INNER JOIN,
WHERE [Link]
);
= [Link]
with less duplication from Table 2.
SELECT *
FROM Table_1 t1
Table 1 Table 2
WHERE NOT EXISTS (SELECT 1
6 FROM
WHERE
Table_2
[Link]
);
=
t2
[Link]
ANTI SEMI JOIN
SQL Join Types Created by
SELECT *
FROM Table_1 t1
Table 1 Table 2 LEFT JOIN Table_2 t2
7
ON [Link] = [Link]
WHERE [Link] is null;
LEFT OUTER JOIN with exclusion — replacement for a NOT IN
SELECT *
FROM Table_1 t1
Table 2 Table 2 RIGHT JOIN Table_2 t2
8
ON [Link] = [Link]
WHERE [Link] is null;
RIGHT OUTER JOIN with exclusion — replacement for a NOT IN
SELECT *
Table 1 Table 2
FROM Table_1 t1
9 FULL
ON
OUTER
[Link]
JOIN
= [Link];
Table_2 t2
FULL OUTER JOIN
SQL Join Types Created by
SELECT *
Table 1 Table 2 FROM Table_1 t1
CROSS JOIN Table_2 t2;
10 CROSS JOIN, like a FULL OUTER JOIN
with out specifying JOIN condition.
SELECT *
FROM Table_1 t1
Table 1 Table 2 FULL OUTER JOIN Table_2 t2
11
ON [Link] = [Link]
WHERE [Link] is null
OR [Link] is null;
FULL OUTER JOIN with exclusion — replacement for a double NOT IN
Table 3
SELECT *
FROM Table_1 t1
INNER JOIN Table_2 t2
12
Table 1 Table 2
ON [Link] = [Link]
INNER JOIN Table_3 t3
ON [Link] = [Link];
Two INNER JOINS
SQL Join Types Created by
Table 3
SELECT *
FROM Table_1 t1
FULL OUTER JOIN Table_2 t2
13
Table 1 Table 2
ON [Link] = [Link]
FULL OUTER JOIN Table_3 t3
ON [Link] = [Link];
Two FULL OUTER JOINS
SELECT *
Table 3
FROM Table_1 t1
INNER JOIN Table_2 t2
14
ON [Link] = [Link]
Table 1 Table 2
LEFT OUTER JOIN Table_3 t3
ON [Link] = [Link];
INNER JOIN and LEFT OUTER JOIN
SELECT *
Table 3
FROM Table_1 t1
LEFT JOIN Table_2 t2
ON [Link] = [Link]
15
Table 1 Table 2
LEFT JOIN Table_3 t3
ON [Link] = [Link];
Two LEFT OUTER JOINS
