SQL: Join Statements

When you’re working with a large database, the data will probably be organized in different tables. If you’re using SQL statements to query the database you’ll need to use JOIN statements in order to get all the relevant data in one place. There are several different types of JOINs, Left, Right, Inner, and Outer. They each do something different, and they can be a powerful tool if you understand how to use them. For this example we’ll work with two tables, one with costumer info and one with order info:

Customers:

Orders:

Notice how the orders have barely any information on the customers, but all the data is contained in the Customers table. This is because if we store all the customers data in a separate table we can access it for many different uses, but we don’t put it directly in the Orders table, because then any u[dates would have to be done in multiple places. Instead we can just store them separately and use JOINs. Here is a basic Join statement:

SELECT *
FROM Orders
LEFT JOIN Customers ON Orders.Customer_ID=Customers.Customer_ID;

Here we have a LEFT JOIN, which takes everything from the left table(the first table mentioned), and matches everything it can from the right table. In this Join all information from the left table will be included, but information from the right table can be excluded. As seen here JOIN statements are often followed with an ON operator, which specifies what column or columns should be used to match data from one table to the other. There is also a RIGHT JOIN, as demonstrated here:

SELECT *
FROM Orders
RIGHT JOIN Customers ON Orders.Customer_ID=Customers.Customer_ID;

Now we have all information from the right table and all matching information from the left table. Notice how there is now another row with the information for a customer who has no orders, but because we’re including all information from the right table it has included the customer anyway and filled out their order information with null values. RIGHT JOINs are not used very often, since you can usually just reverse the order of the tables and use a LEFT JOIN as normal. INNER JOINs are another common way of joining tables. An INNER JOIN only returns the data that has a match in both tables, so it will exclude any entries from either table that don’t match the other table:

SELECT *
FROM Orders
INNER JOIN Customers ON Orders.Customer_ID=Customers.Customer_ID;

This excludes the customer that has no orders, since they don’t match any entries in the Orders table. Likewise, if there were an order that did not match any existing customer ID, that order would not be displayed either. The opposite of this is an OUTER JOIN, which displays all entries from both tables, whether they match the other table or not:

SELECT *
FROM Orders
OUTER JOIN Customers ON Orders.Customer_ID=Customers.Customer_ID;

Now it displays the customer with no orders, and if there were any orders with no matching customers they would be displayed as well. JOIN statements can be paired with queries to pick out specific information from the joined tables. There are many ways to incorporate to other tools of querying into a JOIN statement, which means they can be very powerful if you know how to use them.