SQL-JOIN

 

The join clause combines columns of one table to that of another to create a single table. Join matches up a column with one table to a column in another table. A join query does not alter either table, but temporarily combines data from each table to be viewed as a single table. There are three types of join statements, inner, left, and right.

We will be using our employees table from previous examples, and a new table to track sales of each employee called invoices.

Our invoices table is set up with 3 fields, EmployeeID, Sale, and Price. If we were a business owner we now have a means to track what was sold, and by whom and we can bring this information together using an inner join clause.

Inner Join: An inner join returns all rows that result in a match such as the example above.

SQL Code:

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID

SQL Table:

Lastname	Firstname	Sale	Price
Johnson	David	HOT DOG	1.99
Hively	Jessica	LG SFT DRK	1.49
Davis	Julie	CD SLD	3.99
Davis	Julie	CD SLD	3.99

We haven't changed or updated any information in either of our tables but we were able to fashion together a new table using a conditional that matches one table column to another.

SQL - Left Join

A Left join returns all rows of the left of the conditional even if there is no right column to match.

SQL Code:

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
LEFT JOIN invoices
ON employees.id = invoices.EmployeeID

SQL Table:

Lastname	Firstname	Sale	Price
Johnson	David	HOT DOG	1.99
Hively	Jessica	LG SFT DRK	1.49
Hicks	Freddy
Harris	Joel
Davis	Julie	CD SLD	3.99
Davis	Julie	CD SLD	3.99

This would be a great way to track sales per person per day if the invoice table had a date field as well.

SQL - Right Join

A right join will display rows on the right side of the conditional that may or may not have a match.

SQL Code:

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
RIGHT JOIN invoices
ON employees.id = invoices.EmployeeID

SQL Table:

Lastname	Firstname	Sale	Price
Davis	Julie	CD SLD	3.99
Davis	Julie	CD SLD	3.99
Johnson	David	HOT DOG	1.99
		HOT DOG	1.99
Hively	Jessica	LG SFT DRK	1.49
		LG SFT DRK	1.49

This would happen generally if perhaps nobody recieved credit for the sale or the sale was credited to the store by default.