SQL - Inner 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.

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

SQL Table:

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.

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

SQL Table:

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.

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

SQL Table:

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

• Go Back
• Continue