SQL JOINS

Joins in SQL

What are joins?

JOINS are used to take rows from two or more tables and combine those rows together based on column(s) from each table. There are several types of JOINs in SQL and each type determine which rows are combined.

What types of joins exist?

When using a join to combines from two tables. Sometimes rows in the first table will not be a will not match rose in the other table. Depending on weather you want only matching rows or you want to include unmatched rows in the result of your join will determine which type of join that you use. For example an INNER JOIN will only show rows that match each other in both tables. A FULL JOIN will show all rows from both tables even if they match or not.

With JOINs a key concept with joins is thinking of the tables by position.  One table will be the consider as the “LEFT TABLE” and the other table as the “RIGHT TABLE”. Depending on which table a row exists in depending on if a row exists in the left or right table can impact if it is included or excluded from the resulting set.

INNER JOIN	A row will only be included in the result set when the row from the left table matches a row in the right table. This join is the most frequently used join.
LEFT JOIN	With the left join all rows that exist in the left table will be included in the result set regardless if they match with a row in the right table but rose in the right table will be excluded if they do not match a row from the left table. when a row from the left table does not have a match from the right table the values are set as null.
RIGHT JOIN	the right join works exactly like the left join except all rows from the right table are included regardless if they match with a row from the left table.
FULL JOIN	the full join will combine all rows from the left table and all rows from the right table all rows are included in the result set regardless if they have a matching row in the opposite table.
CROSS JOIN	A cross join is when you want to combine all rows from the left table with all rows from the right table. This is also known as a cartesian product.

How to use a Join.

Join clauses are placed after the FROM clause in the SQL query. Following the join an ON clause is used to specify which columns are used to compare rows between the left and right table.

SELECT * FROM LeftTable AS [L] INNER JOIN RightTable AS [R] ON L.Column = R.Column

They following diagram will help to show how the different types of joins function. The tables below are from a mock business sales database.

The [Product] table lists the products that are available for sale.

The [Sales Detail] table holds a transaction history on products sold and whom bought them.

The [Customer] table lists customers that are registered with the business.

Below are some examples of how to use joins.

INNER JOIN EXAMPLE

Say we want to see just the ProductName, CustomerId, and Quantity for just the products that have sold. We don’t want to include any product that has never sold. We would use an INNER JOIN to get this:

SELECT P.ProductName       ,S.CustomerId       ,S.Qty FROM dbo.Product AS [P] INNER JOIN dbo.SalesDetail AS [S] ON P.ProductId = S.ProductId

The above query is using the join to read the rows from the Product table and then reads all of the rows from the SalesDetails.  It then compares those rows and only includes them in the result if they have a success match.

Rows that do not have a matching row are excluded from the result. Notice that the wrench from the product table was excluded from the query result.

LEFT JOIN EXAMPLE

The LEFT JOIN includes all rows from the left table regardless if they match a row from the right table. This time we want to see all products even if they have a corresponding sale or not. The above query could be changed to use a LEFT JOIN.

SELECT P.ProductName       ,S.CustomerId       ,S.Qty FROM dbo.Product AS [P] LEFT JOIN dbo.SalesDetail AS [S] ON P.ProductId = S.ProductId

This time the wrench is included in the result. When a row does not have a match the columns from the right table will show NULL. The value NULL means the value is unknown, this is not the same as nothing.

RIGHT JOIN EXAMPLE

The RIGHT JOIN is like the LEFT JOIN. The difference is that the right table is the dominant table in the join.

In this example we can use a RIGHT JOIN to show me all customers and any sales associated with those customers.

SELECT   C.CustomerId                  ,C.FirstName               ,S.SaleId               ,S.CurrentPrice FROM dbo.SalesDetail AS [S] RIGHT JOIN dbo.Customer AS [C] ON S.CustomerId = C.CustomerId

Many queries are often written to use a LEFT JOIN instead of a RIGHT JOIN. The above query could have easily been changed to use a LEFT JOIN by switching the order of the Customer and SalesDetail table.

FULL JOIN EXAMPLE

With the FULL JOIN all rows will be include in the query result even if they don’t have a  matching row in the other table. With this example we will add an extra row to the  salesdetail table.

INSERT INTO dbo.SalesDetail VALUES ( 5, 0, 5, 1, 3.5 )

This creates a scenario where both tables have a row that doesn’t match with the other table. Here we want to see all rows between the SalesDetail and Customer table. We also want to include all rows that don’t match.

SELECT * FROM dbo.SalesDetail AS [S] FULL JOIN dbo.Customer AS [C] ON S.CustomerId = C.CustomerId

CROSS JOIN EXAMPLE

The final join is a CROSS join. This join is different than the previous joins mentioned because it does not compare rows between the two table. It combines every row from left and the right table. One practical use for this join is to create mock data. We could use a CROSS JOIN to do this. Using the customer table we want to create a list of names.

SELECT F.FirstName, L.LastName FROM dbo.Customer AS [F] CROSS JOIN dbo.Customer AS [L]

It then repeats this for the second row, the third row, and so on until all rows are combined. With the customer table you would get a result like this.

It is possible for a table to be used more than once in with a join. Tables can even be joined to themselves. This is also known as a SELF JOIN.