{"id":97917,"date":"2023-09-25T00:00:29","date_gmt":"2023-09-25T00:00:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97917"},"modified":"2026-04-15T18:54:08","modified_gmt":"2026-04-15T18:54:08","slug":"mysql-joins","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/mysql-joins\/","title":{"rendered":"MySQL Joins Explained: INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN"},"content":{"rendered":"\n

Executive Summary<\/h2>\n\n\n\n

MySQL joins combine rows from two or more tables based on related column values. This article covers all five MySQL join types: INNER JOIN (returns only matching rows from both tables), LEFT OUTER JOIN (all rows from the left table, matched rows from the right), RIGHT OUTER JOIN (all rows from the right table, matched rows from the left), FULL OUTER JOIN (all rows from both tables, matched and unmatched – simulated in MySQL using UNION), and CROSS JOIN (every row from the left table combined with every row from the right, producing a Cartesian product). Each type is explained with working MySQL 8 examples using a sample travel database.<\/strong><\/p>\n\n\n

This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here<\/a>.<\/strong><\/p>\n\n\n\n\n

Tables in a MySQL database are commonly related to one another, often in multiple ways, and it is only by linking the tables together that you can derive meaningful information from the data. To connect these tables together, you can use the JOIN<\/code> clause, which you include in your SELECT<\/code>, DELETE<\/code>, or UPDATE<\/code> statements. The clause provides a structure for connecting the data from multiple tables, letting you specify which tables to join and under what conditions to join them.<\/p>\n\n\n\n

MySQL supports three basic types of joins: inner joins, outer joins, and cross joins. Outer joins can be further broken down to left outer joins and right outer joins. You can also use left and right joins together to create full outer joins. In this article, I explain how to add joins to your SELECT<\/code> statements and provide examples that demonstrate how they work. Each example retrieves data from the manufacturers<\/code> and airplanes<\/code> tables in the travel<\/code> database, which you\u2019ve seen in previous articles in this series.<\/p>\n\n\n\n

Note: The examples in this article are based on a local instance of MySQL that hosts a very simple database. The last section of the article\u2014\u201cAppendix: Preparing your MySQL environment\u201d\u2014provides information about how I set up my environment and includes a SQL script for creating the database I used when building these examples.<\/p>\n\n\n\n

MySQL inner joins<\/h2>\n\n\n\n

An inner join can retrieve matching data from multiple tables based on one or more columns that are common to both tables. For example, the manufacturers<\/code> and airplanes<\/code> tables in the travel<\/code> database each contain the manufacturer_id<\/code> column. You can create an inner join that links the data in the two tables together based on the values in those columns. The join will return all rows with matching manufacturer_id<\/code> values. The following figure shows how you might visualize an inner join between the two tables.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

<\/p>\n\n\n\n

The left circle represents the data in the manufacturers<\/code> table, and the right circle represents the data in the airplanes<\/code> table. The area in which they intersect is where the manufacturer_id<\/code> values in the manufacturers<\/code> table are equal to the manufacturer_id<\/code> values in the airplanes<\/code> table.<\/p>\n\n\n\n

Note: if you are well acquainted with Venn diagrams, you know they generally work with complete sets of data. They help visualize which rows will be returned in the join operation but note that only the column values in the join condition are part of the intersection (for an INNER JOIN operation) and the other combinations that will be included. <\/em><\/p>\n\n\n\n

An inner join returns only the matching rows from the two tables and excludes all other rows. You\u2019ll see this in action shortly, but first take a look at the following syntax, which represents a SELECT<\/code> statement and its INNER<\/code> JOIN<\/code> clause at their most basic:<\/p>\n\n\n\n

SELECT select_list\nFROM tbl_1 [[AS] tbl_1_alias]\n  [INNER] JOIN tbl_2 [[AS] tbl_2_alias]\n  [ON tbl_1_col = tbl_2_col];<\/pre>\n\n\n\n
The first two lines in the syntax are similar to most other SELECT<\/code> statements. They define the SELECT<\/code> clause and the initial part of the FROM<\/code> clause. The third line represents the start of JOIN<\/code> clause, which is actually a subclause in the FROM<\/code> clause. The JOIN<\/code> clause specifies the name of the second table. This is the table that will be joined to the first table.<\/p>\n\n\n\n
The fourth line in the syntax shows the ON<\/code> clause. Although the clause is technically optional, it is typically included when defining an inner join because it will behave as if the join criteria is always the Boolean value of True<\/code>. Later in the article, I\u2019ll discuss joins that don\u2019t include the ON<\/code> clause on purpose, but for now, assume that you\u2019ll be including it all of your queries that you expect to be an INNER JOIN<\/code>. The clause defines a search condition that determines how the tables are joined.<\/p>\n\n\n\n
The syntax shows how the ON<\/code> clause is commonly structured. It defines a value equality expression which states that a column in the first table must equal a column in the second table. You can create other types of expressions, but for this article, we\u2019ll focus only on value equality expressions because those are the most common.<\/p>\n\n\n\n
The following example demonstrates how the various syntax elements plug into an actual SELECT<\/code> statement:<\/p>\n\n\n\n
SELECT m.manufacturer, a.plane, a.engine_type, a.icao_code\nFROM manufacturers AS m INNER JOIN airplanes AS a\n  ON m.manufacturer_id = a. manufacturer_id;<\/pre>\n\n\n\n
After the SELECT<\/code> clause, the statement defines a FROM<\/code> clause, which starts by specifying the manufacturers<\/code> table and assigning it the m<\/code> alias. A table alias can be useful when referencing the table\u2019s individual columns in other clauses, such as the SELECT<\/code> clause (and essential if you need to reference the same table twice). Otherwise, you would need to include the entire table name to ensure that there is no ambiguity between columns from different table. Theoretically, you do not need to qualify a name if it is unique among the joined tables, but many database and development teams consider its inclusion a best practice.<\/p>\n\n\n\n
The FROM<\/code> clause then goes on to define the INNER<\/code> JOIN<\/code> clause. It identifies airplanes<\/code> as the second table and assigns it the alias a<\/code>. Next comes the ON<\/code> clause and its search condition, which specifies that the m.manufacturer_id<\/code> values must equal the a.manufacturer_id<\/code> values for the rows to be returned. In other words, the clause limits the statement\u2019s results to only those rows with matching manufacturer_id<\/code> values. The following figure shows the data returned by the SELECT<\/code> statement.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Because the query returns only those rows with matching manufacturer_id<\/code> values, the results do not include manufacturers in the manufacturers<\/code> table for which there are no matching planes in the airplanes<\/code> table, nor do they include planes in the airplanes<\/code> table for which there are no matching manufacturers in the manufacturers<\/code> table.<\/p>\n\n\n\n
Now let\u2019s move on to the AS<\/code> keyword, which the example above uses when defining the table aliases. You do not need to include this keyword. For example, you can recast the previous SELECT<\/code> statement as follows:<\/p>\n\n\n\n
SELECT m.manufacturer, a.plane, a.engine_type, a.icao_code\nFROM manufacturers m INNER JOIN airplanes a\n  ON m.manufacturer_id = a. manufacturer_id;<\/pre>\n\n\n\n
You also do not need to include the INNER<\/code> keyword in your JOIN<\/code> clause. When JOIN<\/code> is used without INNER<\/code>, MySQL assumes that you want to perform an inner join. The following example returns the same results as the previous two SELECT<\/code> statements:<\/p>\n\n\n\n
SELECT m.manufacturer, a.plane, a.engine_type, a.icao_code\nFROM manufacturers m JOIN airplanes a\n  ON m.manufacturer_id = a. manufacturer_id;<\/pre>\n\n\n\n
The fact that you do not need to include the INNER<\/code> keyword indicates that MySQL considers an inner join to be the most natural type of join. In fact, MySQL supports something called a \u201cnatural join,\u201d which you can use in place of an inner join when the compared columns have the same name and datatype, as in the following example:<\/p>\n\n\n\n
SELECT m.manufacturer, a.plane, a.engine_type, a.icao_code\nFROM manufacturers m NATURAL JOIN airplanes a;<\/pre>\n\n\n\n
The SELECT<\/code> statement returns the same results as the previous statements even though it does not include an ON<\/code> clause. Be aware, however, that a natural join, unlike an inner join, removes duplicate columns, such as those you get with a SELECT<\/code> *<\/code> query. In this case, the natural join returns only one manufacturer_id<\/code> column, rather than one from each table. In contrast, the inner join returns both columns. Also note that if your tables share other column names (name, row_last_modified_time, for example,) a NATURAL JOIN<\/code> will not work properly.<\/p>\n\n\n\n
Note: MySQL joins are a complex topic. This article focuses only on creating joins that combine two tables, basing the joins on a single set of matching columns. While you can only join two tables at a time, you can join more than two tables in a statement, and you can base your joins on more than one set of matching columns. You can also define other types of search criteria in your ON<\/code> clauses (other than value equality). For more information about joins, see the MySQL documentation, starting with the JOIN Clause<\/a> topic.<\/p>\n\n\n\n
The examples that we\u2019ve looked at up to this point have included only a SELECT<\/code> clause and a FROM<\/code> clause, which contained the join definition. You can include other clauses, however, just like you can in any SELECT<\/code> statement. For example, the following SELECT<\/code> statement includes a WHERE<\/code> clause and ORDER<\/code> BY<\/code> clause:<\/p>\n\n\n\n
SELECT m.manufacturer, a.plane, a.engine_type, a.max_weight\nFROM manufacturers AS m INNER JOIN airplanes AS a\n  ON m.manufacturer_id = a. manufacturer_id\nWHERE a.max_weight < 10000\nORDER BY a.max_weight DESC;<\/pre>\n\n\n\n
The statement now returns only six rows, which are shown in the following figure. As expected, all the returned data meets the condition defined in the WHERE<\/code> clause.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
You can also group and aggregate the data returned by your joined tables. The next SELECT<\/code> statement groups the data based on the manufacturer_id<\/code> values in the manufacturers<\/code> table and then aggregates the data in those groups:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, \n  ROUND(AVG(a.max_weight)) AS avg_weight\nFROM manufacturers AS m INNER JOIN airplanes AS a\n  ON m.manufacturer_id = a. manufacturer_id\nGROUP BY m.manufacturer_id\nORDER BY avg_weight DESC;<\/pre>\n\n\n\n
In this case, the SELECT<\/code> clause calculates the average max_weight<\/code> value for the planes associated with each manufacturer and assigns the avg_weight<\/code> alias to the generated column. The following figure shows the results returned by the SELECT<\/code> statement.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Be aware of the cardinality of rows in your output when you are aggregating data. For example, this was the raw data when we joined the tables together:<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Note: If you count the number of manufacturers using this set of data, you will get 12, while there are just 4. Generally, the table with the key value you are joining on will be the one that you need to use in the <\/em>GROUP BY<\/code> clause, and the table referencing that table will be the one that you need to use in aggregate functions like <\/em>AVG<\/code>, <\/em>SUM<\/code>, <\/em>MIN<\/code>, <\/em>MAX<\/code>, etc. <\/em><\/p>\n\n\n\n
MySQL also supports the USING<\/code> clause when defining a join condition, which you use in place of the ON<\/code> clause. You can include the USING<\/code> clause only if the matching columns have the same names and are configured with the same data type. This is useful when the same name is used in both tables, but you have additional columns with the same name as well. The USING<\/code> clause is safer to use than a NATURAL JOIN<\/code> in reusable code because it is not susceptible to new, duplicated columns.<\/p>\n\n\n\n
The manufacturer_id<\/code> columns in the manufacturers<\/code> and airplanes<\/code> tables meet the requirement, so you can recast the previous SELECT<\/code> statement as follows:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, \n  ROUND(AVG(max_weight)) AS avg_weight\nFROM manufacturers AS m INNER JOIN airplanes AS a\n  USING (manufacturer_id)\nGROUP BY m.manufacturer_id\nORDER BY avg_weight DESC;<\/pre>\n\n\n\n
When specifying the column in the USING<\/code> clause, you must enclose it in parentheses. In addition, you should not qualify the column name with a table name or alias as you do in an ON<\/code> clause. You simply specify the column name.<\/p>\n\n\n\n
MySQL left and right outer joins<\/h2>\n\n\n\n
In some cases, you might want to retrieve the non-matching data from one of the joined tables, along with the matching data from both tables. To do so, you should define an outer join rather than an inner join.<\/p>\n\n\n\n
MySQL supports two types of outer joins: left outer joins and right outer joins. The \u201cleft\u201d refers to the left table specified in the JOIN<\/code> clause, and the \u201cright\u201d refers to the right table specified in the clause. In a left outer join, non-matching rows in the left table are included in the results, and in a right outer join, non-matching rows in the right table are included in the results.<\/p>\n\n\n\n
For example, the following figure shows you how you might visualize a left outer join between the manufacturers<\/code> and airplanes<\/code> tables.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
As you saw with inner joins, the left circle represents the manufacturers<\/code> table, which is the \u201cleft\u201d table, and the right circle represents the airplanes<\/code> table, which is the \u201cright\u201d table. The area in which they intersect is where the manufacturer_id<\/code> values in both tables match. The join also incorporates the non-matching data in the left table, which is indicated by the solid green.<\/p>\n\n\n\n
Creating an outer join works much like an inner join. The following syntax shows a basic SELECT<\/code> statement that includes the LEFT<\/code> OUTER<\/code> JOIN<\/code> clause:<\/p>\n\n\n\n
SELECT select_list\nFROM tbl_1 [[AS] tbl_1_alias]\n  LEFT [OUTER] JOIN tbl_2 [[AS] tbl_2_alias]\n  [ON tbl_1_col = tbl_2_col];<\/pre>\n\n\n\n
The syntax is similar to an inner join except for the use of the LEFT<\/code> keyword and optional OUTER<\/code> keyword. The syntax is meant to show only the most basic elements necessary to perform a left outer join. It does not reflect other clauses that you can add to your SELECT<\/code> statements, such as WHERE<\/code> or GROUP<\/code> BY<\/code>. For example, the following query creates a left outer join with the manufacturers<\/code> table specified as the left table:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, a.plane, \n  a.engine_type, a.max_weight, a.wingspan\nFROM manufacturers AS m \n  LEFT OUTER JOIN airplanes AS a\n  ON m.manufacturer_id = a. manufacturer_id\nORDER BY a.plane;<\/pre>\n\n\n\n
In addition to the JOIN<\/code> clause, the also includes an ORDER<\/code> BY<\/code> clause that sorts the data by the plane<\/code> column, in ascending order. The SELECT<\/code> statement returns the result set shown in the following figure.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
In addition to the matching rows, the statement returns all rows from the manufacturers<\/code> table for which there is no matching data in the airplanes<\/code> table. You can identify these rows by the NULL<\/code> values in the columns from the airplanes<\/code> table. In this case, the first three rows have no matching data. However, the rest of the rows in the left table match rows in the right table, so those results look much like you saw with the inner joins.<\/p>\n\n\n\n
MySQL also lets you include a USING<\/code> clause in your outer join statements, just like you can your inner joins. For example, you can recast the previous SELECT<\/code> statement to include a USING<\/code> clause, which returns the same results:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, a.plane, \n  a.engine_type, a.max_weight, a.wingspan\nFROM manufacturers AS m \n  LEFT OUTER JOIN airplanes AS a\n  USING (manufacturer_id)\nORDER BY a.plane;<\/pre>\n\n\n\n
A right outer join works just like a left outer join except that it returns non-matching rows from the right table rather than the left table, as illustrated in the following figure. Once again, the area in which the two circles intersect is where the manufacturer_id<\/code> values in the two tables match. The solid part of the right circle represents the non-matching data in the right table.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
A right outer join is simply the reverse of a left outer join. They can be used interchangeably by reversing the table order. In fact, MySQL documentation recommends that you stick with left joins for all your outer joins: \u201cRIGHT<\/code> JOIN<\/code> works analogously to LEFT<\/code> JOIN<\/code>. To keep code portable across databases, it is recommended that you use LEFT<\/code> JOIN<\/code> instead of RIGHT<\/code> JOIN<\/code>.\u201d If you take this approach, you need only reverse the order of the tables when building your queries.<\/p>\n\n\n\n
While generally a good approach, despite the MySQL recommendations there might be times when you need to use a right outer join, such as when you are joining multiple tables, or when you\u2019re constructing a full outer join (which I\u2019ll demonstrate shortly). The following syntax shows a basic SELECT<\/code> statement that includes the RIGHT<\/code> OUTER<\/code> JOIN<\/code> clause:<\/p>\n\n\n\n
SELECT select_list\nFROM tbl_1 [[AS] tbl_1_alias]\n  RIGHT [OUTER] JOIN tbl_2 [[AS] tbl_2_alias]\n  [ON tbl_1_col = tbl_2_col];<\/pre>\n\n\n\n
The syntax differs from the left outer join only in the use of the RIGHT<\/code> keyword rather than the LEFT<\/code> keyword. The SELECT<\/code> statement in the following example demonstrates what this looks like:<\/p>\n\n\n\n
SELECT m.manufacturer_id AS man_tbl_id, m.manufacturer, \n  a.plane_id, a.plane, a.manufacturer_id AS air_tbl_id\nFROM manufacturers AS m \n  RIGHT OUTER JOIN airplanes AS a\n  ON m.manufacturer_id = a. manufacturer_id\nORDER BY a.plane;<\/pre>\n\n\n\n
In this example, I included the manufacturer_id<\/code> column from both tables, providing a different alias for each one. This makes it easier to distinguish between the two columns and to see which manufacturer_id<\/code> values exist in the airplanes<\/code> table that do not exist in the manufacturers<\/code> table. The following figure shows the results returned by the SELECT<\/code> statement.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
As the figure demonstrates, the airplanes<\/code> table contains six non-matching rows, which are indicated by the NULL<\/code> values in the man_tbl_id<\/code> and manufacturer<\/code> columns, both of which come from the manufacturers<\/code> table.<\/p>\n\n\n\n
Note that the examples for this article are not necessarily best practices in database design. However, to demonstrate some of the different join types, it was necessary to have rows in each table that did not relate to one another.<\/em><\/p>\n\n\n\n
In some cases, you might need to perform an outer join that returns only the non-matching rows in one of the tables. For example, you might want to know which rows in the airplanes<\/code> table reference manufacturer_id<\/code> values that do not exist in the manufacturers<\/code> table, as illustrated in the following figure.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
You can retrieve this information by adding a WHERE<\/code> clause to your SELECT<\/code> statement that checks the data for nullability. In effect, you\u2019re taking advantage of the NULL<\/code> values returned by your query when there are no corresponding matches. For example, the following SELECT<\/code> statement is the same as the previous one except that it includes a WHERE<\/code> clause:<\/p>\n\n\n\n
SELECT a.plane_id, a.plane, a.manufacturer_id\nFROM manufacturers AS m \n  RIGHT OUTER JOIN airplanes AS a\n  ON m.manufacturer_id = a. manufacturer_id\nWHERE m.manufacturer IS NULL\nORDER BY a.plane;<\/pre>\n\n\n\n
The WHERE<\/code> clause uses the IS<\/code> NULL<\/code> operator to check whether the manufacturer_id<\/code> column contains a NULL<\/code> value. If it does, the expression evaluates to true and the row is returned, giving us the results shown in the following figure.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Checking for NULL<\/code> values in this way provides a handy method for identifying what might be anomalous data, which could be the result of a bulk-loading operation, merging tables from a legacy system, a bug at the application or data level, or some other process.<\/p>\n\n\n\n
Where filtering by NULL<\/code> finds the values that don’t match, be careful about filtering rows in the WHERE<\/code> clause unless you realize what you are doing. For example, executing the following:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, a.plane, \n  a.engine_type, a.max_weight, a.wingspan\nFROM manufacturers AS m \n  LEFT OUTER JOIN airplanes AS a\n  USING (manufacturer_id)\nWHERE a.manufacturer_id = 104 -- where clause filter\nORDER BY a.plane;<\/pre>\n\n\n\n
Will just return the rows with manufacturer = 104<\/code>. This, in effect, turns this into an INNER JOIN<\/code>, because all of the mismatched rows from the LEFT OUTER JOIN<\/code> will be eliminated because their manufacturer_id<\/code> is NULL<\/code> and hence, does not equal 104<\/code>. However, change your join to:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, a.plane, \n  a.engine_type, a.max_weight, a.wingspan\nFROM manufacturers AS m \n  LEFT OUTER JOIN airplanes AS a\n  ON m.manufacturer_id = a.manufacturer_id\n     AND a.manufacturer_id = 104\nORDER BY a.plane;<\/pre>\n\n\n\n
And the output is changed again, but perhaps not exactly what you wanted as now every manufacturer is returned, but only rows that match the a.manufacturer_id = 104<\/code> predicate are returned.<\/p>\n\n\n\n
\"A<\/figure>\n\n\n\n
<\/p>\n\n\n\n
MySQL full outer joins<\/h2>\n\n\n\n
A full outer join returns all data from the two connected tables. In addition to the matching rows from both tables, the join returns unmatched rows in the left table and unmatched rows in the right table, as illustrated in the following figure. In this case, the result set includes manufacturers with no corresponding planes in the airplanes<\/code> table and planes with no corresponding manufacturers in the manufacturing<\/code> table, along with the matching data from both tables.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
MySQL does not support full outer joins like some database systems. You can get around this by using a UNION<\/code> operator to join a left outer join statement and a right outer join statement, as in the following example:<\/p>\n\n\n\n
SELECT m.manufacturer, a.plane, a.engine_type\nFROM manufacturers AS m \n  LEFT OUTER JOIN airplanes AS a\n  USING (manufacturer_id)\nUNION ALL\nSELECT m.manufacturer, a.plane, a.engine_type\nFROM manufacturers AS m \n  RIGHT OUTER JOIN airplanes AS a\n  USING (manufacturer_id);<\/pre>\n\n\n\n
The individual SELECT<\/code> statements work just like the earlier examples of left and right outer joins. The UNION<\/code> ALL<\/code> operator joins the two statements together to return a single result set, which is shown in the following figure.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The results include both matching rows and non-matching rows, which are indicated by the NULL<\/code> values. However, because the two statements are joined together, the results also include duplicate rows. For example, the Airbus planes are each listed twice, as are all the other planes. You can eliminate the duplicates by using the UNION<\/code> operator without the ALL<\/code> qualifier, as in the following example:<\/p>\n\n\n\n
SELECT m.manufacturer, a.plane, a.engine_type\nFROM manufacturers AS m \n  LEFT OUTER JOIN airplanes AS a\n  USING (manufacturer_id)\nUNION\nSELECT m.manufacturer, a.plane, a.engine_type\nFROM manufacturers AS m \n  RIGHT OUTER JOIN airplanes AS a\n  USING (manufacturer_id);<\/pre>\n\n\n\n
Now the statement returns only distinct rows, whether the data is matching or non-matching, as shown in the following figure:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Although MySQL doesn\u2019t provide a specific clause for creating full outer joins, such as you\u2019ll find in SQL Server or other database systems, there might be times when you need a more complete picture than what either a left outer join or right outer join can provide on its own. Using a UNION<\/code> operator to combine the two outer joins is a handy way to accomplish this.<\/p>\n\n\n\n
MySQL cross joins<\/h2>\n\n\n\n
Another type of join that MySQL supports is the cross join, which matches each row in the left table to each row in the right table to produce what is referred as a Cartesian product<\/em>. The following figure illustrates a cross join between the manufacturers<\/code> table and the airplanes<\/code> table.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
The figure shows the various ways that each value pair can be matched. For example, the 101<\/code> value in the manufacturers<\/code> table is matched to every value in the airplanes<\/code> table, and the 1001<\/code> value in the airplanes<\/code> table is matched to every value in the manufacturers<\/code> table. This process continues for each value in both tables until all values have been matched up. The following syntax shows a basic SELECT<\/code> statement that includes the CROSS<\/code> JOIN<\/code> clause:<\/p>\n\n\n\n
SELECT select_list\nFROM tbl_1 [[AS] tbl_1_alias]\n  CROSS JOIN tbl_2 [[AS] tbl_2_alias]\n  [ON tbl_1_col = tbl_2_col];<\/pre>\n\n\n\n
Except for the CROSS<\/code> keyword, the syntax elements should all look familiar to you. That said, the cross join is not as clear-cut as it might appear. Notice that the syntax, like the previous join examples, indicates that the ON<\/code> clause is optional. A cross join statement returns a Cartesian product only if it does not include this clause. If the clause is included, the statement behaves much like a regular inner join.<\/p>\n\n\n\n
Some sources imply that a MySQL cross join is strictly limited to producing a Cartesian product, but this is clearly not the case, as indicated by its support for the ON<\/code> clause (or USING<\/code> clause). According to MySQL documentation, \u201cJOIN<\/code>, CROSS<\/code> JOIN<\/code>, and INNER<\/code> JOIN<\/code> are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER<\/code> JOIN<\/code> is used with an ON<\/code> clause, CROSS<\/code> JOIN<\/code> is used otherwise.\u201d<\/p>\n\n\n\n
Let\u2019s look at a few examples to better understand how the cross join works. The following SELECT<\/code> statement defines a cross join without including an ON<\/code> clause or USING<\/code> clause:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, \n  a.plane_id, a.plane, a.manufacturer_id\nFROM manufacturers AS m \n  CROSS JOIN airplanes AS a\nORDER BY m.manufacturer_id, a.plane_id;<\/pre>\n\n\n\n
The statement returns a Cartesian product for the manufacturers<\/code> and airplanes<\/code> tables. The manufacturers<\/code> table contains seven rows, and the airplanes<\/code> table contains 18 rows. Because a cross join matches each row in the first table to each row in the second table, you can multiply the two row counts together (7 x 18) to calculate the total number of rows that should be included in the Cartesian product. In this case, the total is 126, which is the number of rows returned by the SELECT<\/code> statement.<\/p>\n\n\n\n
The following figure shows a small portion of these results. Notice that there is an Airbus row for each plane, even if that plane is not manufactured by Airbus. The same is true for all manufacturers. The cross join simply combines the individual rows together.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This in itself should seem straightforward enough, and by all accounts, this is how you should use the cross join\u2014to return a Cartesian product. However, because CROSS<\/code> JOIN<\/code> and INNER<\/code> JOIN<\/code> are syntactic equivalents, you can achieve the same results with an inner join that does not include an ON<\/code> clause or USING<\/code> clause:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, \n  a.plane_id, a.plane, a.manufacturer_id\nFROM manufacturers AS m \n  INNER JOIN airplanes AS a\nORDER BY m.manufacturer_id, a.plane_id;<\/pre>\n\n\n\n
The statement returns the same Cartesian product as the previous example. However, this is not the only way to return these results. You can instead specify the two table names, separated by a comma, and drop the JOIN<\/code> clause altogether:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, \n  a.plane_id, a.plane, a.manufacturer_id\nFROM manufacturers m, airplanes a\nORDER BY m.manufacturer_id, a.plane_id;<\/pre>\n\n\n\n
Once again, you\u2019ll end up with the same Cartesian product that was returned by the previous two examples.<\/p>\n\n\n\n
Note: MySQL documentation warns that the precedence of a comma operator is less than the actual <\/em>JOIN<\/em><\/code> keywords. This might be an issue if you mix join types in a statement that combines more than two tables. <\/em><\/p>\n\n\n\n
In addition, the comma operator can be used only to generate a Cartesian product. It cannot be used with an ON<\/code> or USING<\/code> clause. This is not the case for a cross join, which can include either of these clauses, just like an inner join. The following examples shows a cross join that contains a USING<\/code> clause:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, \n  a.plane_id, a.plane, a.manufacturer_id\nFROM manufacturers AS m \n  CROSS JOIN airplanes AS a\n  USING (manufacturer_id)\nORDER BY m.manufacturer_id;<\/pre>\n\n\n\n
By adding the USING<\/code> clause, the statement now returns only 12 rows, rather than 126. The results are shown in the following figure.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
You can return the same results with the following inner join statement, which also incorporates the USING<\/code> clause:<\/p>\n\n\n\n
SELECT m.manufacturer_id, m.manufacturer, \n  a.plane_id, a.plane, a.manufacturer_id\nFROM manufacturers AS m \n  INNER JOIN airplanes AS a\n  USING (manufacturer_id)\nORDER BY m.manufacturer_id;<\/pre>\n\n\n\n
Despite the fact that CROSS<\/code> JOIN<\/code> and INNER<\/code> JOIN<\/code> are syntactic equivalents, the general consensus is to use cross joins when you want to work directly with the Cartesian product and use inner joins when you want to qualify the join with an ON<\/code> or USING<\/code> clause. This doesn\u2019t preclude the use of other SELECT<\/code> clauses in your cross join statements (such as WHERE<\/code> or ORDER<\/code> BY<\/code>), but it does serve as general guideline for differentiating between the two when you want to generate a Cartesian product.<\/p>\n\n\n\n
Getting started with MySQL joins<\/h2>\n\n\n\n
The topic of MySQL joins is a complex one, as I noted earlier. A single article is not nearly enough to cover all the various ways you can use joins to combine data from multiple tables. Although the article provides you with a jumping-off point, it by no means covers every aspect of join creation. You can, for example, combine different types of joins into a single query, define joins in your DELETE<\/code> and UPDATE<\/code> statements, join three or more tables in a single statement, or base your joins on two or more sets of matching columns. To do all this, however, you need a good foundation on which to build, and this article might help you get started with that process.<\/p>\n\n\n\n
Appendix: Preparing your MySQL environment<\/h2>\n\n\n\n
For the examples for this article, I used a Mac computer that was set up with a local instance of MySQL 8.0.29 (Community Server edition). I also used MySQL Workbench to interface with MySQL. Through Workbench, I created the travel<\/code> database, added the manufacturers<\/code> and airplanes<\/code> tables, and inserted test data into the tables.<\/p>\n\n\n\n
If you want to try out the examples for yourself, start by running the following script against your MySQL instance:<\/p>\n\n\n\n
DROP DATABASE IF EXISTS travel;\nCREATE DATABASE travel;\nUSE travel;\nCREATE TABLE manufacturers (\n  manufacturer_id INT UNSIGNED NOT NULL,\n  manufacturer VARCHAR(50) NOT NULL,\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  last_update TIMESTAMP NOT NULL \n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n  PRIMARY KEY (manufacturer_id) );\nCREATE TABLE airplanes (\n  plane_id INT UNSIGNED NOT NULL,\n  plane VARCHAR(50) NOT NULL,\n  manufacturer_id INT UNSIGNED NOT NULL,\n  engine_type VARCHAR(50) NOT NULL,\n  engine_count TINYINT NOT NULL,\n  max_weight MEDIUMINT UNSIGNED NOT NULL,\n  wingspan DECIMAL(5,2) NOT NULL,\n  plane_length DECIMAL(5,2) NOT NULL,\n  parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,\n  icao_code CHAR(4) NOT NULL,\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  last_update TIMESTAMP NOT NULL \n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n  PRIMARY KEY (plane_id));<\/pre>\n\n\n\n
The script creates the travel<\/code> database and adds the manufacturers<\/code> and airplanes<\/code> tables. In previous articles, I had defined a primary key on the airplanes<\/code> table that referenced the manufacturers<\/code> table. For this article, I did not include the foreign key so it would be easier to demonstrate various join operations.<\/p>\n\n\n\n
After you create the tables, you should run the following INSERT<\/code> statements:<\/p>\n\n\n\n
INSERT INTO manufacturers (manufacturer_id, manufacturer)\nVALUES (101,'Airbus'), (102,'Beagle Aircraft Limited'), (103,'Beechcraft'),\n  (104,'Boeing'), (105,'Cessna'), (106,'Embraer'), (107,'Gulfstream');\nINSERT INTO airplanes \n  (plane_id, plane, manufacturer_id, engine_type, engine_count, \n    wingspan, plane_length, max_weight, icao_code)\nVALUES\n  (1001,'A340-600',101,'Jet',4,208.17,247.24,837756,'A346'),\n  (1002,'A350-800 XWB',101,'Jet',2,212.42,198.58,546700,'A358'),\n  (1003,'A350-900',101,'Jet',2,212.42,219.16,617295,'A359'),\n  (1004,'A.109 Airedale',102,'Piston',1,36.33,26.33,2750,'AIRD'),\n  (1005,'A.61 Terrier',102,'Piston',1,36,23.25,2400,'AUS6'),\n  (1006,'B.121 Pup',102,'Piston',1,31,23.17,1600,'PUP'),\n  (1007,'Baron 56 TC Turbo Baron',103,'Piston',2,37.83,28,5990,'BE56'),\n  (1008,'Bonanza 33 (F33A)',103,'Piston',1,33.5,26.67,3500,'BE33'),\n  (1009,'Bonanza 35 (G35)',103,'Piston',1,32.83,25.17,3125,'BE35'),\n  (1010,'747-8F',104,'Jet',4,224.42,250.17,987000,'B748'),\n  (1011,'747-SP',104,'Jet',4,195.67,184.75,696000,'B74S'),\n  (1012,'757-300',104,'Jet',2,124.83,178.58,270000,'B753'),\n  (1013,'PA-28R-200 Cherokee Arrow',121,'Piston',1,30.00,23.50,2600,'P28R'),\n  (1014,'PA-18-150 Super Cub',121,'Piston',1,35.29,22.50,1750,'PA18'),\n  (1015,'PA-24-180 Comanche',121,'Piston',1,36.00,24.79,2550,'PA24'),\n  (1016,'M20D Master',136,'Piston',1,35.00,23.25,2500,'M20P'),\n  (1017,'M20F Executive 21',136,'Piston',1,36.00,24.00,2740,'M20P'),\n  (1018,'M20L PFM',136,'Piston',1,36.42,26.75,2900,'M20P');<\/pre>\n\n\n\n
The INSERT<\/code> statements first populate the manufacturers<\/code> table and then the airplanes<\/code> table. The statement for each table intentionally includes data without corresponding records in the other table. For example, the manufacturers<\/code> table includes manufacturers with no products in the airplanes<\/code> table, and the airplanes<\/code> table includes products for manufacturers that do not exist in the manufacturers<\/code> table. These inclusions will help us test various types of join conditions.<\/p>\n\n\n\n
\n    
FAQs: MySQL joins<\/h2>\n\n                        
1. What is the difference between INNER JOIN and OUTER JOIN in MySQL?<\/h3>\n            
\n                
INNER JOIN returns only rows where there is a match in both tables. If a row in the left table has no matching row in the right table (or vice versa), it is excluded from the result. OUTER JOINs include non-matching rows: LEFT OUTER JOIN (or LEFT JOIN) returns all rows from the left table and matched rows from the right – unmatched right-table columns appear as NULL. RIGHT OUTER JOIN returns all rows from the right table and matched rows from the left. FULL OUTER JOIN returns all rows from both tables (not natively supported in MySQL – simulate with UNION of LEFT and RIGHT joins).<\/p>\n            <\/div>\n                    
2. When should I use a LEFT JOIN instead of an INNER JOIN in MySQL?<\/h3>\n            
\n                
Use LEFT JOIN when you want to include all rows from the left (primary) table even if they have no matching rows in the right table. Common use cases: finding customers who have no orders (WHERE right_table.id IS NULL after a LEFT JOIN), retrieving all products whether or not they have associated inventory records, or reporting across tables where the relationship is optional rather than required. Use INNER JOIN when you only want rows with a confirmed relationship in both tables and have no interest in unmatched rows.<\/p>\n            <\/div>\n                    
3. How do I do a FULL OUTER JOIN in MySQL?<\/h3>\n            
\n                
MySQL does not natively support FULL OUTER JOIN syntax (unlike PostgreSQL and SQL Server). Simulate it with UNION: SELECT * FROM table_a LEFT JOIN table_b ON table_a.id = table_b.id UNION SELECT * FROM table_a RIGHT JOIN table_b ON table_a.id = table_b.id; UNION removes duplicates between the two result sets, giving the equivalent of a full outer join. UNION ALL would include duplicate matching rows from both queries – use UNION (deduplicating) for the correct FULL OUTER JOIN equivalent.<\/p>\n            <\/div>\n                    
4. What is a CROSS JOIN in MySQL and when is it useful?<\/h3>\n            
\n                
A CROSS JOIN returns every combination of rows from two tables – also called a Cartesian product. If table_a has 5 rows and table_b has 4 rows, the CROSS JOIN returns 20 rows. Use CROSS JOIN when you intentionally want all combinations: generating a combination matrix, creating a calendar of all date\/time combinations, or testing with all possible parameter pairings. Avoid accidental CROSS JOINs from forgotten JOIN conditions – they can return millions of rows and cause significant performance issues.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
A complete guide to MySQL joins: INNER JOIN for matching rows, LEFT and RIGHT OUTER JOINs for non-matching rows, FULL OUTER JOIN simulation, and CROSS JOIN for Cartesian products – with T-SQL examples and setup scripts.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[53,145792],"tags":[],"coauthors":[6779],"class_list":["post-97917","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97917","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=97917"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97917\/revisions"}],"predecessor-version":[{"id":109832,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97917\/revisions\/109832"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97917"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97917"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97917"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97917"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}