Last active
August 29, 2015 14:05
-
-
Save jjokela/c4759889f84d98003242 to your computer and use it in GitHub Desktop.
SQL Joins explained
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment