Funcionamiento de "JOIN"

Definicion: 

La sentencia join en SQL permite combinar registros de dos o más tablas en una base de datos relacional. En el Lenguaje de Consultas Estructurado (SQL), hay tres tipo de JOIN: interno, externo, y cruzado.

En casos especiales una tabla puede unirse a sí misma, produciendo una auto-combinación, SELF-JOIN.

Matemáticamente, JOIN es composición relacional, la operación fundamental en el álgebra relacional, y generalizando es una función de composición.

 

Ejemplo:

 

Customers:

CustomerID	FirstName	LastName	Email	DOB	Phone
1	John	Smith	[e-mail]	2/4/1968	626 222-2222
2	Steven	Goldfish	[e-mail]	4/4/1974	323 455-4545
3	Paula	Brown	[e-mail]	5/24/1978	416 323-3232
4	James	Smith	[e-mail]	20/10/1980	416 323-8888

 

Sales:

CustomerID	Date	SaleAmount
2	5/6/2004	$100.22
1	5/7/2004	$99.95
3	5/7/2004	$122.95
3	5/13/2004	$100.00
4	5/22/2004	$555.55

As you can see those 2 tables have common field called CustomerID and thanks to that we can extract information from both tables by matching their CustomerID columns.

Consider the following SQL statement:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers, Sales WHERE Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName

The SQL expression above will select all distinct customers (their first and last names) and the total respective amount of dollars they have spent. 
The SQL JOIN condition has been specified after the SQL WHERE clause and says that the 2 tables have to be matched by their respective CustomerID columns.

Here is the result of this SQL statement:

FirstName	LastName	SalesPerCustomers
John	Smith	$99.95
Steven	Goldfish	$100.22
Paula	Brown	$222.95
James	Smith	$555.55

The SQL statement above can be re-written using the SQL JOIN clause like this:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName

There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short "INNER JOIN" = "JOIN" (note that different databases have different syntax for their JOIN clauses).

The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn't made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above.

If the Sales table has the following rows:

CustomerID	Date	SaleAmount
2	5/6/2004	$100.22
1	5/6/2004	$99.95

And we use the same SQL JOIN statement from above:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName

We'll get the following result:

FirstName	LastName	SalesPerCustomers
John	Smith	$99.95
Steven	Goldfish	$100.22

Even though Paula and James are listed as customers in the Customers table they won't be displayed because they haven't purchased anything yet.

But what if you want to display all the customers and their sales, no matter if they have ordered something or not? We’ll do that with the help of SQL OUTER JOIN clause.

The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

If we slightly modify our last SQL statement to:

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer FROM Customers LEFT JOIN Sales ON Customers.CustomerID = Sales.CustomerID GROUP BY Customers.FirstName, Customers.LastName

and the Sales table still has the following rows:

CustomerID	Date	SaleAmount
2	5/6/2004	$100.22
1	5/6/2004	$99.95

The result will be the following:

FirstName	LastName	SalesPerCustomers
John	Smith	$99.95
Steven	Goldfish	$100.22
Paula	Brown	NULL
James	Smith	NULL

As you can see we have selected everything from the Customers (first table). For all rows from Customers, which don’t have a match in the Sales (second table), the SalesPerCustomer column has amount NULL (NULL means a column contains nothing).

The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in ourSQL JOIN statement).