SQL Joins explained

gistfile1.sql

-- Table contents

--	Customers
--	CustomerID		CustomerName	Country
--	1				      Jarmo			    Suomi
--	2				      Risto			    Ruotsi
--	3				      Erno			    Englanti
--	4				      Testeri			  Testimaa

--	Orders
--	OrderID		CustomerID	OrderDate
--	1			    1			      2014-01-01
--	2			    1			      2014-02-02
--	3			    2			      2013-01-01
--	4			    3			      2012-09-09
--	5			    NULL	    	NULL

/* Orders [] Customers */

/* LEFT JOIN */
/* Selects all from Orders and where Orders and Customers intersect */
/* 'Everything from A, and all of A+B '*/
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID

/* LEFT JOIN */
/* Selects only those from Orders that don't intersect with Orders and Customers*/
/* 'Only found in A, but not in A+B' */
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID is null

/* RIGHT JOIN */
/* Selects all from Customers and where Customers and Orders intersect */
/* 'Everything from B, and all of B+A' */
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID

/* RIGHT JOIN */
/* Selects only those from Customers that don't intersect with Orders*/
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID is null

/* FULL OUTER JOIN */
/* Selects ALL from Orders and Customers */
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
FULL OUTER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID

/* FULL OUTER JOIN */
/* Selects all from Orders and Customers, except their intersection */
/* 'Everything from A and B, but not A+B' */
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
FULL OUTER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID is null 
OR Orders.CustomerID is null

/* INNER JOIN */
/* Selects Orders and Customers intersection */
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID