Join On Join
Andrey Gershun edited this page Jan 9, 2016
·
2 revisions
#JOIN ON JOIN
This page created for understanding results produced with combination of joins.
First we will create three tables and fill them with values:
CREATE TABLE one (id NVARCHAR(3));
CREATE TABLE two (id NVARCHAR(3));
CREATE TABLE three (id NVARCHAR(3));
INSERT INTO one VALUES ('A'),('AB'),('AC'),('ABC');
INSERT INTO two VALUES ('B'),('AB'),('BC'),('ABC');
INSERT INTO three VALUES ('C'),('BC'),('AC'),('ABC');SELECT one.id AS a, two.id AS b FROM one INNER JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id;returns
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one INNER JOIN two ON one.id = two.id LEFT OUTER JOIN three ON two.id = three.id;returns:
AB AB NULL
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one INNER JOIN two ON one.id = two.id RIGHT JOIN three ON two.id = three.id;returns:
NULL NULL C
NULL NULL BC
NULL NULL AC
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one INNER JOIN two ON one.id = two.id FULL OUTER JOIN three ON two.id = three.id;returns:
AB AB NULL
ABC ABC ABC
NULL NULL C
NULL NULL BC
NULL NULL AC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one LEFT JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id;returns
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one LEFT JOIN two ON one.id = two.id LEFT JOIN three ON two.id = three.id;returns
A NULL NULL
AB AB NULL
AC NULL NULL
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one LEFT JOIN two ON one.id = two.id RIGHT JOIN three ON two.id = three.id;returns
NULL NULL C
NULL NULL BC
NULL NULL AC
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one LEFT JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id;returns
A NULL NULL
AB AB NULL
AC NULL NULL
ABC ABC ABC
NULL NULL C
NULL NULL BC
NULL NULL AC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one RIGHT JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id;returns:
NULL BC BC
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one RIGHT JOIN two ON one.id = two.id LEFT JOIN three ON two.id = three.id;returns:
NULL B NULL
AB AB NULL
NULL BC BC
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one RIGHT JOIN two ON one.id = two.id RIGHT JOIN three ON two.id = three.id;returns
NULL NULL C
NULL BC BC
NULL NULL AC
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one RIGHT JOIN two ON one.id = two.id RIGHT JOIN three ON two.id = three.id;returns
NULL B NULL
AB AB NULL
NULL BC BC
ABC ABC ABC
NULL NULL C
NULL NULL AC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one FULL OUTER JOIN two ON one.id = two.id INNER JOIN three ON two.id = three.id;returns:
NULL BC BC
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one FULL OUTER JOIN two ON one.id = two.id LEFT JOIN three ON two.id = three.id;returns:
A NULL NULL
AB AB NULL
AC NULL NULL
ABC ABC ABC
NULL B NULL
NULL BC BC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one FULL OUTER JOIN two ON one.id = two.id RIGHT JOIN three ON two.id = three.id;returns:
NULL NULL C
NULL BC BC
NULL NULL AC
ABC ABC ABC
SELECT one.id AS a, two.id AS b, three.id AS c FROM one FULL OUTER JOIN two ON one.id = two.id FULL OUTER JOIN three ON two.id = three.id;returns
A NULL NULL
AB AB NULL
AC NULL NULL
ABC ABC ABC
NULL B NULL
NULL BC BC
NULL NULL C
NULL NULL AC
© 2014-2023, Andrey Gershun & Mathias Rangel Wulff
Please let us know if something could be described better by submitting an issue