There are four joins in SQL in reference to SQL Server. Namely, they are Inner Join, Left Outer Join, Right Outer Join, Full Outer Join. The Inner Join will bring the common records between two tables e.g. Table A, and Table B, where the conditions mentioned are satisfied.
On the other hand, the Left Outer Join, is used, when you need to retrieve all records of Table A, but Table B, which is child or foreign key table to Table A, does not necessarily contain all rows satisfying the conditions. So in the case of Left Outer, for those rows of Table A, we will output the rows from Table B, with null values.
The case of Right Outer Join is opposite to Left Outer Join, when all rows from Table B will be shown, even they do not have a link or condition satisfied with Table A, so against those rows, null value will return from Table B.
Full outer join is the union of Left Outer and Right Outer join, meaning all the rows will return.
There is interesting fact here, that Inner Join, and Right Outer Join are the same to each other, and Left Outer Join and Full Outer Join are the same when Table A is parent, and Table B is child, and Table B does not contain any orphan records. This is also proven by comparing the number of records e.g. as by given queries below which are run in Dynamics AX 2012 for retrieving the Vendors and their numbering based upon the transactions they have.
/* INNER
JOIN EXAMPLE - SHOWING VENDORS BY MAXIMUM PURCHASES SORTED*/
SELECT VT.ACCOUNTNUM, PARTY.NAME, SUM(AMOUNTCUR)
AS TOTALAMT
FROM VENDTABLE VT
JOIN VENDTRANS VTR
ON VT.ACCOUNTNUM = VTR.ACCOUNTNUM
JOIN DIRPARTYTABLE PARTY
ON PARTY.RECID = VT.PARTY
GROUP BY VT.ACCOUNTNUM, PARTY.NAME
HAVING SUM(AMOUNTCUR) > 100000
ORDER BY TOTALAMT ASC;
/* LEFT
OUTER JOIN EXAMPLE - SHOWING ALL VENDORS AND THE PURCHASES SORTED*/
SELECT VT.ACCOUNTNUM, PARTY.NAME, ISNULL(SUM(AMOUNTCUR),0) TOTALAMT
FROM VENDTABLE VT
LEFT JOIN
VENDTRANS VTR
ON VT.ACCOUNTNUM = VTR.ACCOUNTNUM
JOIN DIRPARTYTABLE PARTY
ON PARTY.RECID = VT.PARTY
GROUP BY VT.ACCOUNTNUM, PARTY.NAME
ORDER BY TOTALAMT ASC;
/* RIGHT
OUTER JOIN EXAMPLE - SHOWING ALL VENDORS AND THE PURCHASES SORTED - LOOKS SAME
AS INNER JOIN*/
SELECT VT.ACCOUNTNUM, PARTY.NAME, ISNULL(SUM(AMOUNTCUR),0) TOTALAMT
FROM VENDTABLE VT
RIGHT JOIN
VENDTRANS VTR
ON VT.ACCOUNTNUM = VTR.ACCOUNTNUM
JOIN DIRPARTYTABLE PARTY
ON PARTY.RECID = VT.PARTY
GROUP BY VT.ACCOUNTNUM, PARTY.NAME
ORDER BY TOTALAMT ASC;
/* full
OUTER JOIN EXAMPLE - SHOWING ALL VENDORS AND THE PURCHASES SORTED*/
SELECT VT.ACCOUNTNUM, PARTY.NAME, ISNULL(SUM(AMOUNTCUR),0) TOTALAMT
FROM VENDTABLE VT
FULL OUTER JOIN VENDTRANS VTR
ON VT.ACCOUNTNUM = VTR.ACCOUNTNUM
JOIN DIRPARTYTABLE PARTY
ON PARTY.RECID = VT.PARTY
GROUP BY VT.ACCOUNTNUM, PARTY.NAME
ORDER BY TOTALAMT ASC;