# Using joins to combine data from different tables in MySQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. Joining tables allows you to associate and bring together related data stored in different tables in MySQL. Canonical URL: https://www.prisma.io/dataguide/mysql/reading-and-querying-data/joining-tables Authors: Justin Ellingwood Content revision: 75693dca60995e658856c83944bdd8f8d23a8896f557e5054de9ddbad41ecb80 Source revision: 3209fc5696440a92402a9e3aaaab817ef4a9fb2c ## Introduction Though it's often useful to separate data into discrete tables for performance and consistency purposes, you often need to consult data from multiple tables to answer certain requests. _Joining_ tables is a way of combining the data from various tables by matching each record based on common field values. There are a few different types of [joins](https://www.prisma.io/dataguide/types/relational/what-are-joins-in-sql), which offer various ways of combining table records. In this article, we'll cover how MySQL implements joins and discuss the scenarios in which each is most useful. ## What are joins? In short, [_joins_](https://www.prisma.io/dataguide/intro/database-glossary#join) are a way of displaying data from multiple tables. They do this by stitching together records from different sources based on matching values in certain columns. Each resulting row consists of a record from the first table combined with a row from the second table, based on one or more columns in each table having the same value. The basic syntax of a join looks like this: ```sql SELECT * FROM ; ``` In a join, each resulting row is constructed by including all of the columns of the first table followed by all of the columns from the second table. The `SELECT` portion of the query can be used to specify the exact columns you wish to display. Multiple rows may be constructed from the original tables if the values in the columns used for comparison are not unique. For example, imagine you have a column being compared from the first table that has two records with a value of "red". Matched with this is a column from the second table that has three rows with that value. The join will produce six different rows for that value representing the various combinations that can be achieved. The type of join and the join conditions determine how each row that is displayed is constructed. This impacts what happens to the rows from each table that do and do _not_ have a match on the join condition. For the sake of convenience, many joins match the primary key on one table with an associated foreign key on the second table. Although primary and foreign keys are only used by the database system to maintain consistency guarantees, their relationship often makes them a good candidate for join conditions. ## Different types of joins Various types of joins are available, each of which will potentially produce different results. Understanding how each type is constructed will help you determine which is appropriate for different scenarios. ### Inner and cross joins The default join is called an [_inner join_](https://www.prisma.io/dataguide/intro/database-glossary#inner-join). In MySQL, this can be specified using either `INNER JOIN`, just `JOIN`, or `CROSS JOIN`. For other database systems, `INNER JOIN` and `CROSS JOIN` are often two separate concepts, but MySQL implements them in the same construct. Here is a typical example demonstrating the syntax of an inner join: ```sql SELECT * FROM table_1 [INNER] JOIN table_2 ON table_1.id = table_2.table_1_id; ``` An inner join is the most restrictive type of join because it only displays rows created by combining rows from each table. Any rows in the constituent tables that did not have a matching counterpart in the other table are removed from the results. For example, if the first table has a value of "blue" in the comparison column, and the second table has no record with that value, that row will be suppressed from the output. If you represent the results as a Venn diagram of the component tables, an inner join allows you to represent the overlapping area of the two circles. None of values that only existed in one of the tables are displayed. As mentioned above, MySQL also uses this format to produce cross joins. In MySQL, you can produce a cross join using an inner join without any match conditions. A cross join does not use any comparisons to determine whether the rows in each table match one another. Instead, results are constructed by simply adding each of the rows from the first table to each of the rows of the second table. This produces a Cartesian product of the rows in two or more tables. In effect, this style of join combines rows from each table unconditionally. So, if each table has three rows, the resulting table would have nine rows containing all of the columns from both tables. For example, if you have a table called `t1` combined with a table called `t2`, each with rows `r1`, `r2`, and `r3`, the result would be nine rows combined like so: ``` t1.r1 + t2.r1 t1.r1 + t2.r2 t1.r1 + t2.r3 t1.r2 + t2.r1 t1.r2 + t2.r2 t1.r2 + t2.r3 t1.r3 + t2.r1 t1.r3 + t2.r2 t1.r3 + t2.r3 ``` ### Left join A [left join](https://www.prisma.io/dataguide/intro/database-glossary#left-join) is a join that shows all of the records found in an inner join, plus all of the _unmatched_ rows from the first table. In MYSQL, this can be specified as a `LEFT OUTER JOIN` or as just a `LEFT JOIN`. The basic syntax of a left join follows this pattern: ```sql SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.id = table_2.table_1_id; ``` A left join is constructed by first performing an inner join to construct rows from all of the matching records in both tables. Afterwards, the unmatched records from the first table are also included. Since each row in a join includes the columns of both tables, the unmatched columns use `NULL` as the value for all of the columns in the second table. If you represent the results as a Venn diagram of the component tables, a left join allows you to represent the entire left circle. The parts of the left circle represented by the intersection between the two circles will have additional data supplemented by the right table. ### Right join A [right join](https://www.prisma.io/dataguide/intro/database-glossary#right-join) is a join that shows all of the records found in an inner join, plus all of the _unmatched_ rows from the second table. In MySQL, this can be specified as a `RIGHT OUTER JOIN` or as just a `RIGHT JOIN`. The basic syntax of a right join follows this pattern: ```sql SELECT * FROM table_1 RIGHT JOIN table_2 ON table_1.id = table_2.table_1_id; ``` A right join is constructed by first performing an inner join to construct rows from all of the matching records in both tables. Afterwards, the unmatched records from the second table are also included. Since each row in a join includes the columns of both tables, the unmatched columns use `NULL` as the value for all of the columns in the first table. If you represent the results as a Venn diagram of the component tables, a right join allows you to represent the entire right circle. The parts of the right circle represented by the intersection between the two circles will have additional data supplemented by the left table. For portability reasons, MySQL recommends you use left joins instead of right joins where possible. ### Full join A [full join](https://www.prisma.io/dataguide/intro/database-glossary#outer-join) is a join that shows all of the records found in an inner join, plus all of the _unmatched_ rows from both component tables. MySQL does not natively implement full joins, but we can emulate the behavior using a few tricks. To replicate the results of a full outer join, we will perform a left join to all of the results that are shared by both tables and all of the unmatched rows from the left table. Then we will use the [`UNION ALL` set operator](https://dev.mysql.com/doc/refman/8.0/en/union.html#union-distinct-all) to combine those results with an "anti-join" for the right table. An "anti-join" is a join operation that exclusively finds the results _not_ in common between tables. The basic syntax of a full join follows this pattern: ```sql ( SELECT * FROM table_1 LEFT JOIN table_2 ON table_1.id = table_2.table_1_id ) UNION ALL ( SELECT * FROM table_1 RIGHT JOIN table_2 ON table_1.id = table_2.table_1_id WHERE table_1.id IS NULL ); ``` Since each row in a join includes the columns of both tables, the unmatched columns use `NULL` as the value for all of the columns in the unmatched other table. If you represent the results as a Venn diagram of the component tables, a full join allows you to represent both of the component circles entirely. The intersection of the two circles will have values supplied by each of the component tables. The parts of the circles outside of the overlapping area will have the values from the table they belong to, using `NULL` to fill in the columns found in the other table. ### Self join A self join is any join that combines the rows of a table with itself. It may not be immediately apparent how this could be useful, but it actually has many common applications. Often, tables describe entities that can fulfill multiple roles in relationship to one another. For instance, if you have a table of `people`, each row could potentially contain a `mother` column that reference other `people` in the table. A self join would allow you to stitch these different rows together by joining a second instance of the table to the first where these values match. Since self joins reference the same table twice, table aliases are required to disambiguate the references. In the example above, for instance, you could join the two instances of the `people` table using the aliases `people AS children` and `people AS mothers`. That way, you can specify which instance of the table you are referring to when defining join conditions. Here is another example, this time representing relationships between employees and managers: ```sql SELECT * FROM people AS employee JOIN people AS manager ON employee.manager_id = manager.id; ``` ## Join conditions When combining tables, the join condition determines how rows will be matched together to form the composite results. The basic premise is to define the columns in each table that must match for the join to occur on that row. ### The `ON` clause The most standard way of defining the conditions for table joins is with the `ON` clause. The `ON` clause uses an equals sign to specify the exact columns from each table that will be compared to determine when a join may occur. MySQL uses the provided columns to stitch together the rows from each table. The `ON` clause is the most verbose, but also the most flexible of the available join conditions. It allows for specificity regardless of how standardized the column names are of each table being combined. The basic syntax of the `ON` clause looks like this: ```sql SELECT * FROM table1 JOIN table2 ON table1.id = table2.ident; ``` Here, the rows from `table1` and `table2` will be joined whenever the `id` column from `table1` matches the `ident` column from `table2`. Because an inner join is used, the results will only show the rows that were joined. Since the query uses the wildcard `*` character, all of the columns from both tables will be displayed. This means that both the `id` column from `table1` and the `ident` column from `table2` will be displayed, even though they have the same exact value by virtue of satisfying the join condition. You can avoid this duplication by calling out the exact columns you wish to display in the `SELECT` column list. ### The `USING` clause The `USING` clause is a shorthand for specifying the conditions of an `ON` clause that can be used when the columns being compared have the same name in both tables. The `USING` clause takes a list, enclosed in parentheses, of the shared column names that should be compared. The general syntax of the `USING` clause uses this format: ```sql SELECT * FROM table1 JOIN table2 USING (id, state); ``` This join combines `table1` with `table2` when two columns that both tables share (`id` and `state`) each have matching values. This same join could be expressed more verbosely using `ON` like this: ```sql SELECT * FROM table1 JOIN table2 ON table1.id = table2.id AND table1.state = table2.state; ``` While both of the above joins would result in the same rows being constructed with the same data present, they would be displayed slightly different. While the `ON` clause includes all of the columns from both tables, the `USING` clause suppresses the duplicate columns. So instead of there being two separate `id` columns and two separate `state` columns (one for each table), the results would just have one of each of the shared columns, followed by all of the other columns provided by `table1` and `table2`. ### The `NATURAL` clause The `NATURAL` clause is yet another shorthand that can further reduce the verbosity of the `USING` clause. A `NATURAL` join does not specify _any_ columns to be matched. Instead, MySQL will automatically join the tables based on all columns that have matching columns in each database. The general syntax of the `NATURAL` join clause looks like this: ```sql SELECT * FROM table1 NATURAL JOIN table2; ``` Assuming that `table1` and `table2` both have columns named `id`, `state`, and `company`, the above query would be equivalent to this query using the `ON` clause: ```sql SELECT * FROM table1 JOIN table2 ON table1.id = table2.id AND table1.state = table2.state AND table1.company = table2.company; ``` And this query using the `USING` clause: ```sql SELECT * FROM table1 JOIN table2 USING (id, state, company); ``` Like the `USING` clause, the `NATURAL` clause suppresses duplicate columns, so there would be only a single instance of each of the joined columns in the results. While the `NATURAL` clause can reduce the verbosity of your queries, care must be exercised when using it. Because the columns used for joining the tables are automatically calculated, if the columns in the component tables change, the results can be vastly different due to new join conditions. ## Join conditions and the `WHERE` clause Join conditions share many characteristics with the comparisons used to filter rows of data using `WHERE` clauses. Both constructs define expressions that must evaluate to true for the row to be considered. Because of this, it's not always intuitive what the difference is between including additional comparisons in a `WHERE` construct versus defining them within the join clause itself. In order to understand the differences that will result, we have to take a look at the order in which MySQL processes different portions of a query. In this case, the predicates in the join condition are processed first to construct the virtual joined table in memory. After this stage, the expressions within the `WHERE` clause are evaluated to filter the resulting rows. As an example, suppose that we have two tables called `customers` and `orders` that we need to join together. We want to join the two tables by matching the `customers.id` column with the `orders.customer_id` column. Additionally, we're interested in the rows in the `orders` table that have a `product_id` of 12345. Given the above requirements, we have two conditions that we care about. The way we express these conditions, however, will determine the results we receive. First, let's use both as the join conditions for a `LEFT JOIN`: ```sql SELECT customers.id AS customers_id, customers.name, orders.id AS orders_id, orders.product_id FROM customers LEFT JOIN orders ON customers.id = orders.customers_id AND orders.product_id = 12345; ``` The results could potentially look something like this: ``` +--------------+----------+-----------+------------+ | customers_id | name | orders_id | product_id | +--------------+----------+-----------+------------+ | 20 | Early Co | NULL | NULL | | 320 | Other Co | 680 | 12345 | | 4380 | Acme Co | 182 | 12345 | | 4380 | Acme Co | 480 | 12345 | | 8033 | Big Co | NULL | NULL | +--------------+----------+-----------+------------+ 5 rows in set (0.00 sec) ``` MySQL arrived at this result by performing the following operations: 1. Combine any rows in the `customers` table with the `orders` table where: - `customers.id` matches `orders.customers_id`. - `orders.product_id` matches 12345 2. Because we are using a left join, include any _unmatched_ rows from the left table (`customers`), padding out the columns from the right table (`orders`) with `NULL` values. 3. Display only the columns listed in the `SELECT` column specification. The outcome is that all of our joined rows match both of the conditions that we are looking for. However, the left join causes MySQL to also include any rows from the first table that did not satisfy the join condition. This results in "left over" rows that don't seem to follow the apparent intent of the query. If we move the second query (`orders.product_id` = 12345) to a `WHERE` clause, instead of including it as a join condition, we get different results: ```sql SELECT customers.id AS customers_id, customers.name, orders.id AS orders_id, orders.product_id FROM customers LEFT JOIN orders ON customers.id = orders.customers_id WHERE orders.product_id = 12345; ``` This time, only three rows are displayed: ``` +--------------+----------+-----------+------------+ | customers_id | name | orders_id | product_id | +--------------+----------+-----------+------------+ | 4380 | Acme Co | 182 | 12345 | | 4380 | Acme Co | 480 | 12345 | | 320 | Other Co | 680 | 12345 | +--------------+----------+-----------+------------+ 3 rows in set (0.00 sec) ``` The order in which the comparisons are executed is the reason for these differences. This time, MySQL processes the query like this: 1. Combine any rows in the `customers` table with the `orders` table where `customers.id` matches `orders.customers_id`. 2. Because we are using a left join, include any _unmatched_ rows from the left table (`customers`), padding out the columns from the right table (`orders`) with `NULL` values. 3. Evaluate the `WHERE` clause to remove any rows that do not have 12345 as the value for the `orders.product_id` column. 4. Display only the columns listed in the `SELECT` column specification. This time, even though we are using a left join, the `WHERE` clause truncates the results by filtering out all of the rows without the correct `product_id`. Because any unmatched rows would have `product_id` set to `NULL`, this removes all of the unmatched rows that were populated by the left join. It also removes any of the rows that were matched by the join condition that did not pass this second round of checks. Understanding the basic process that MySQL uses to execute your queries can help you avoid some easy-to-make but difficult-to-debug mistakes as you work with your data. ## Conclusion In this article, we discussed what joins are and how MySQL implements them as a way of combining records from multiple tables. We covered the different types of joins available and the way that different conditions like the `ON` and `WHERE` clauses affect the way that the database constructs results. As you get more familiar with joins, you'll be able to use them as a regular part of your toolkit to pull in data from various sources and stitch together pieces of information to create a more full picture. Joins help bring together the data that organization principles and performance considerations may separate. Learning how to effectively use joins can help you bring together data regardless of how it's organized in the system.