- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINs in SQL
In this article, we will learn the basics of joining tables in SQL. We will learn about Left, Right, Inner, and Outer JOINs and give examples of how to use them.
Data in an RDBMS (Relational Database Management System) is grouped into tables. These tables have a rigid definition of the type of data that can be stored in them. To connect different tables, and thus different types of data that may relate to each other, we will use the JOIN clause.
Types of JOINs
There are four basic types of JOINs: INNER, OUTER, LEFT, and RIGHT. When connecting two tables they will each return a different subset of the corresponding tables based on the condition in the ON clause.
- LEFT and RIGHT JOINs perform a similar task. They both return the entirety of a single table with information that is related to the second table.
- INNER and OUTER JOINs perform very different tasks. INNER JOINs produces a very limited result, while OUTER returns a complete dataset.
In this example, we are using the example of a sales department for this tutorial, employing three tables: salespeople, orders, and customers.
The salespeople table will have three columns: id, name, and commission rate.
Next, the orders table will contain four columns: the order id, the total cost of the order, the customer, and, if available, the salesperson.
The customer table will contain two columns: id and basic contact information.
So, we now have multiple tables of information that are useful to different people in different ways. Using these three tables (salespeople, orders, and customer), we will provide examples of how each of the JOINs might be useful.
Probably the most commonly used type of JOIN is a LEFT JOIN. If you think of the two tables being joined, the one mentioned in the FROM clause is on the left. The one mentioned in the JOIN clause is on the right. In a LEFT JOIN, every row from the LEFT (or FROM) table is returned in the result and is linked with corresponding rows from the RIGHT (or JOIN) table that matches the ON clause.
Keep in mind that some rows on the LEFT may not have matching data on the RIGHT. In that case, the fields that would have been filled in from the RIGHT table in the result will be populated by a NULL value.
Also, if multiple rows in the RIGHT table match the rows from the LEFT table, then multiple rows will be included in the result set. A Venn diagram shows how a LEFT JOIN would look visualized:
The Marketing Director asks you for a report on all the orders broken down by customer. You can use a LEFT JOIN for this:
SELECT * FROM orders LEFT JOIN customer ON orders.customer_id = customer.id;
This query asks for all the data in the “orders” table linked with the rows in the “customer” table, where the id of the customer is equal to the customer_id in order. The result would look like this:
Notice that for the Widgets LLC customer, there are three entries because they had three orders. Jolly Inc. placed two orders, and Acme Inc placed one. Cheapo does not show up on this list because no orders were placed.
The RIGHT JOIN is very similar to the LEFT JOIN, except it returns every row from the RIGHT (JOIN) table and only corresponding rows from the LEFT (FROM) table. Again, if there is no data in the LEFT table, then those columns will be populated with NULLvalues.
If there are multiple rows in the LEFT table, then there will be multiple rows in the result set. Its Venn diagram would look like this:
If management wants a report with EVERY customer; even if they did not place an order, we could use a RIGHT JOIN.
SELECT * FROM orders RIGHT JOIN customer ON orders.customer_id = customer.id;
Again we start with the “orders” table and JOIN it to the customer table. Because we used a RIGHT JOIN we will get at least one row for every entry in the RIGHT (JOIN) table, customer. When there is a match between customer.id and orders.customer_id then the information is filled in. The results include Cheapo Co with NULL values in the order columns:
An INNER JOIN returns only the rows from each column that match the ON clause. If there is not a match on the RIGHT then the rows from the LEFT are excluded and vice versa. The Venn diagram for an INNER JOIN looks like this:
It is payday and the payroll department needs to know how much commission to payout. For this, they will need to know the orders that were placed through each salesperson. Note that not all orders went through a salesperson, so we do not care about those. For this we can use an INNER JOIN:
SELECT *,cost * commission_rate / 100 AS commission_amount FROM orders INNER JOIN salespeople ON orders.salesperson_id = salespeople.id;
Again we start with the orders table. This time we are JOINing it with the salespeople table. If there is an entry in the orders table that matches an entry in the salespeople table then the row will be included. Instead of populating unmatched rows with NULL values, those rows are ignored in the results.
Please note that we are telling the database not only to return the sale amount and the commission rate but also the calculated commission. The results would look like this:
An OUTER JOIN returns everything regardless if there is a match. If you were to combine the results of a LEFT and RIGHT JOIN you would get an OUTER JOIN. The Venn diagram for an OUTER JOIN looks like this:
Now, it is the end of the month and the sales manager wants to know ALL the sales as well as all the commissions that were paid out. For this we will use an OUTER JOIN:
SELECT *,cost * commission_rate / 100 AS commission_amount FROM orders FULL OUTER JOIN salespeople ON orders.salesperson_id = salespeople.id;
Like the payroll report, we start with orders and JOIN to salespeople. The difference is that with an OUTER JOIN you will get results from both the LEFT and RIGHT tables with NULL filled in where there is not a corresponding match. The results look like this:
Now, remember that we said that MariaDB and MySQL do not support OUTER JOIN. We also said that if you add a LEFT JOIN to a RIGHT JOIN you would get an OUTER JOIN. The trick in those two systems is to do just that. We accomplish this with the UNION clause. It adds the results of one query to another:
SELECT *,cost * commission_rate / 100 AS commission_amount FROM orders LEFT JOIN salespeople ON orders.salesperson_id = salespeople.id UNION SELECT *,cost * commission_rate / 100 AS commission_amount FROM orders RIGHT JOIN salespeople ON orders.salesperson_id = salespeople.id;
The results would look like this:
JOIN also allows you to connect more than two tables. If we want a full sales report with customer and salespeople information, we simply do another JOIN at the end.
SELECT * FROM orders LEFT JOIN salespeople ON orders.salesperson_id = salespeople.id LEFT JOIN customer ON customer.id = orders.customer_id;
In this example, we start with the orders table and connect it to the salespeople table much like we did before. The next step is to JOIN the table to the customer table. This will fill in all the information that is linkable to the orders table.
This article is a brief introduction and is not meant to be an exhaustive discussion of how JOIN can be used in SQL.
Click Below To Use This Convenient Coupon Today!
Liquid Web has some of the most powerful database servers in the industry. These servers can be utilized to run the smallest home business up to the largest multi-database clusters for enterprise-scale corporations.
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.