Example
Let's say we have two tables (A and B) and some of their rows match (relative to the given JOIN condition, whatever it may be in the particular case):
We can use various join types to include or exclude matching or non-matching rows from either side, and correctly name the join by picking the corresponding terms from the diagram above.
The examples below use the following test data:
CREATE TABLE A (
X varchar(255) PRIMARY KEY
);
CREATE TABLE B (
Y varchar(255) PRIMARY KEY
);
INSERT INTO A VALUES
('Amy'),
('John'),
('Lisa'),
('Marco'),
('Phil');
INSERT INTO B VALUES
('Lisa'),
('Marco'),
('Phil'),
('Tim'),
('Vincent');
Inner Join
Combines left and right rows that match.
SELECT * FROM A JOIN B ON X = Y;
X Y
------ -----
Lisa Lisa
Marco Marco
Phil Phil
Left Outer Join
Sometimes abbreviated to "left join". Combines left and right rows that match, and includes non-matching left rows.
SELECT * FROM A LEFT JOIN B ON X = Y;
X Y
----- -----
Amy NULL
John NULL
Lisa Lisa
Marco Marco
Phil Phil
Right Outer Join
Sometimes abbreviated to "right join". Combines left and right rows that match, and includes non-matching right rows.
SELECT * FROM A RIGHT JOIN B ON X = Y;
X Y
----- -------
Lisa Lisa
Marco Marco
Phil Phil
NULL Tim
NULL Vincent
Full Outer Join
Sometimes abbreviated to "full join". Union of left and right outer join.
SELECT * FROM A FULL JOIN B ON X = Y;
X Y
----- -------
Amy NULL
John NULL
Lisa Lisa
Marco Marco
Phil Phil
NULL Tim
NULL Vincent
Left Semi Join
Includes left rows that match right rows.
SELECT * FROM A WHERE X IN (SELECT Y FROM B);
X
-----
Lisa
Marco
Phil
Right Semi Join
Includes right rows that match left rows.
SELECT * FROM B WHERE Y IN (SELECT X FROM A);
Y
-----
Lisa
Marco
Phil
As you can see, there is no dedicated IN syntax for left vs. right semi join - we achieve the effect simply by switching the table positions within SQL text.
Left Anti Semi Join
Includes left rows that do not match right rows.
SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);
X
----
Amy
John
WARNING: Be careful if you happen to be using NOT IN on a NULL-able column! More details here.
Right Anti Semi Join
Includes right rows that do not match left rows.
SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A);
Y
-------
Tim
Vincent
As you can see, there is no dedicated NOT IN syntax for left vs. right anti semi join - we achieve the effect simply by switching the table positions within SQL text.
Cross Join
A Cartesian product of all left with all right rows.
SELECT * FROM A CROSS JOIN B;
X Y
----- -------
Amy Lisa
John Lisa
Lisa Lisa
Marco Lisa
Phil Lisa
Amy Marco
John Marco
Lisa Marco
Marco Marco
Phil Marco
Amy Phil
John Phil
Lisa Phil
Marco Phil
Phil Phil
Amy Tim
John Tim
Lisa Tim
Marco Tim
Phil Tim
Amy Vincent
John Vincent
Lisa Vincent
Marco Vincent
Phil Vincent
Cross join is equivalent to an inner join with join condition which always matches, so the following query would have returned the same result:
SELECT * FROM A JOIN B ON 1 = 1;
Self-Join
This simply denotes a table joining with itself. A self-join can be any of the join types discussed above. For example, this is a an inner self-join:
SELECT * FROM A A1 JOIN A A2 ON LEN(A1.X) < LEN(A2.X);
X X
---- -----
Amy John
Amy Lisa
Amy Marco
John Marco
Lisa Marco
Phil Marco
Amy Phil
