{"id":6351,"date":"2017-07-15T08:20:59","date_gmt":"2017-07-15T15:20:59","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6351"},"modified":"2024-01-05T01:33:21","modified_gmt":"2024-01-05T08:33:21","slug":"mysql-join","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-join\/","title":{"rendered":"MySQL Join"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various MySQL join clauses in the SELECT<\/code> statement to query data from two tables.<\/p>\n\n\n\n

Introduction to MySQL join clauses<\/h2>\n\n\n\n

A relational database consists of multiple related tables linking together using common columns, which are known as foreign key<\/a> columns. Because of this, the data in each table is incomplete from the business perspective.<\/p>\n\n\n\n

For example, in the sample database<\/a>, we have the orders<\/code> and orderdetails<\/code> tables that are linked using the orderNumber<\/code> column:<\/p>\n\n\n\n

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

To get complete order information, you need to query data from both orders<\/code> and  orderdetails<\/code> tables.<\/p>\n\n\n\n

That’s why joins come into the play.<\/p>\n\n\n\n

A join is a method of linking data between one (self-join<\/a>) or more tables based on the values of the common column between the tables.<\/p>\n\n\n\n

MySQL supports the following types of joins:<\/p>\n\n\n\n

    \n
  1. Inner join<\/a><\/li>\n\n\n\n
  2. Left join<\/a><\/li>\n\n\n\n
  3. Right join<\/a><\/li>\n\n\n\n
  4. Cross join<\/a><\/li>\n<\/ol>\n\n\n\n

    To join tables, you use the cross join, inner join, left join, or right join clause. The join clause is used in the SELECT<\/a><\/code> statement appeared after the FROM<\/code> clause.<\/p>\n\n\n\n

    Note that MySQL hasn’t supported the FULL OUTER JOIN<\/code> yet.<\/p>\n\n\n\n

    Setting up sample tables<\/h2>\n\n\n\n

    First, create two tables<\/a> called members<\/code> and committees<\/code>:<\/p>\n\n\n

    CREATE<\/span> TABLE<\/span> members (\n    member_id INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(100<\/span>),\n    PRIMARY KEY<\/span> (member_id)\n);\n\nCREATE<\/span> TABLE<\/span> committees (\n    committee_id INT<\/span> AUTO_INCREMENT,\n    name<\/span> VARCHAR<\/span>(100<\/span>),\n    PRIMARY KEY<\/span> (committee_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    INSERT<\/span> INTO<\/span> members(name<\/span>)\nVALUES<\/span>('John'<\/span>),('Jane'<\/span>),('Mary'<\/span>),('David'<\/span>),('Amelia'<\/span>);\n\nINSERT<\/span> INTO<\/span> committees(name<\/span>)\nVALUES<\/span>('John'<\/span>),('Mary'<\/span>),('Amelia'<\/span>),('Joe'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    SELECT<\/span> * FROM<\/span> members;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +-----------+--------+\n| member_id | name   |\n+-----------+--------+\n|         1 | John   |\n|         2 | Jane   |\n|         3 | Mary   |\n|         4 | David  |\n|         5 | Amelia |\n+-----------+--------+\n5 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n
    SELECT<\/span> * FROM<\/span> committees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +--------------+--------+\n| committee_id | name   |\n+--------------+--------+\n|            1 | John   |\n|            2 | Mary   |\n|            3 | Amelia |\n|            4 | Joe    |\n+--------------+--------+\n4 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Some members are committee members, and some are not. On the other hand, some committee members are in the members<\/code> table, some are not.<\/p>\n\n\n\n
    MySQL INNER JOIN clause<\/h2>\n\n\n\n
    The following shows the basic syntax of the inner join clause that joins two tables table_1<\/code> and table_2<\/code>:<\/p>\n\n\n
    SELECT<\/span> column_list\nFROM<\/span> table_1\nINNER<\/span> JOIN<\/span> table_2 ON<\/span> join_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The inner join clause compares each row from the first table with every row from the second table. <\/p>\n\n\n\n
    If values from both rows satisfy the join condition, the inner join clause creates a new row whose column contains all columns of the two rows from both tables and includes this new row in the result set. In other words, the inner join clause includes only matching rows from both tables.<\/p>\n\n\n\n
    If the join condition uses the equality operator (=<\/code>) and the column names in both tables used for matching are the same, and you can use the USING<\/code> clause instead:<\/p>\n\n\n
    SELECT<\/span> column_list\nFROM<\/span> table_1\nINNER<\/span> JOIN<\/span> table_2 USING<\/span> (column_name);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following statement uses an inner join clause to find members who are also the committee members:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nINNER<\/span> JOIN<\/span> committees c ON<\/span> c.name = m.name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +-----------+--------+--------------+-----------+\n| member_id | member | committee_id | committee |\n+-----------+--------+--------------+-----------+\n|         1 | John   |            1 | John      |\n|         3 | Mary   |            2 | Mary      |\n|         5 | Amelia |            3 | Amelia    |\n+-----------+--------+--------------+-----------+\n3 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example, the inner join clause uses the values in the name<\/code> columns in both tables members<\/code> and committees<\/code> to match.<\/p>\n\n\n\n
    The following Venn diagram illustrates the inner join:<\/p>\n\n\n\n
    \"mysql<\/figure>\n\n\n\n
    Because both tables use the same column to match, you can use the USING<\/code> clause as shown in the following query:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nINNER<\/span> JOIN<\/span> committees c USING<\/span>(name<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    MySQL LEFT JOIN clause<\/h2>\n\n\n\n
    Similar to an inner join, a left join<\/a> also requires a join predicate. When joining two tables using a left join, the concepts of left and right tables are introduced.<\/p>\n\n\n\n
    The left join selects data starting from the left table. For each row in the left table, the left join compares with every row in the right table. <\/p>\n\n\n\n
    If the values in the two rows satisfy the join condition, the left join clause creates a new row whose columns contain all columns of the rows in both tables and includes this row in the result set.<\/p>\n\n\n\n
    If the values in the two rows are not matched, the left join clause still creates a new row whose columns contain columns of the row in the left table and NULL<\/code> for columns of the row in the right table.<\/p>\n\n\n\n
    In other words, the left join selects all data from the left table whether there are matching rows exist in the right table or not. <\/p>\n\n\n\n
    In case there are no matching rows from the right table found, the left join uses NULLs for columns of the row from the right table in the result set.<\/p>\n\n\n\n
    Here is the basic syntax of a left join clause that joins two tables:<\/p>\n\n\n
    SELECT<\/span> column_list \nFROM<\/span> table_1 \nLEFT<\/span> JOIN<\/span> table_2 ON<\/span> join_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The left join also supports the USING<\/code> clause if the column used for matching in both tables is the same:<\/p>\n\n\n
    SELECT<\/span> column_list \nFROM<\/span> table_1 \nLEFT<\/span> JOIN<\/span> table_2 USING<\/span> (column_name);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following example uses a left join clause to join the members<\/code> with the committees<\/code> table:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nLEFT<\/span> JOIN<\/span> committees c USING<\/span>(name<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +-----------+--------+--------------+-----------+\n| member_id | member | committee_id | committee |\n+-----------+--------+--------------+-----------+\n|         1 | John   |            1 | John      |\n|         2 | Jane   |         NULL | NULL      |\n|         3 | Mary   |            2 | Mary      |\n|         4 | David  |         NULL | NULL      |\n|         5 | Amelia |            3 | Amelia    |\n+-----------+--------+--------------+-----------+\n5 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following Venn diagram illustrates the left join:<\/p>\n\n\n\n
    \"mysql<\/figure>\n\n\n\n
    This statement uses the left join clause with the USING<\/code> syntax:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nLEFT<\/span> JOIN<\/span> committees c USING<\/span>(name<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nLEFT<\/span> JOIN<\/span> committees c USING<\/span>(name<\/span>)\nWHERE<\/span> c.committee_id IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +-----------+--------+--------------+-----------+\n| member_id | member | committee_id | committee |\n+-----------+--------+--------------+-----------+\n|         2 | Jane   |         NULL | NULL      |\n|         4 | David  |         NULL | NULL      |\n+-----------+--------+--------------+-----------+\n2 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    Generally, this query pattern can find rows in the left table that do not have corresponding rows in the right table.<\/p>\n\n\n\n
    This Venn diagram illustrates how to use the left join to select rows that only exist in the left table:<\/p>\n\n\n\n
    \"mysql<\/figure>\n\n\n\n
    MySQL RIGHT JOIN clause<\/h2>\n\n\n\n
    The right join<\/a> clause is similar to the left join clause except that the treatment of left and right tables is reversed. The right join starts selecting data from the right table instead of the left table.<\/p>\n\n\n\n
    The right join clause selects all rows from the right table and matches rows in the left table. If a row from the right table does not have matching rows from the left table, the column of the left table will have NULL<\/code> in the final result set.<\/p>\n\n\n\n
    Here is the syntax of the right join:<\/p>\n\n\n
    SELECT<\/span> column_list \nFROM<\/span> table_1 \nRIGHT<\/span> JOIN<\/span> table_2 ON<\/span> join_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Similar to the left join clause, the right clause also supports the USING<\/code> syntax:<\/p>\n\n\n
    SELECT<\/span> column_list \nFROM<\/span> table_1 \nRIGHT<\/span> JOIN<\/span> table_2 USING<\/span> (column_name);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    SELECT<\/span> column_list \nFROM<\/span> table_1 \nRIGHT<\/span> JOIN<\/span> table_2 USING<\/span> (column_name)\nWHERE<\/span> column_table_1 IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    This statement uses the right join to join the members<\/code> and committees<\/code> tables:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nRIGHT<\/span> JOIN<\/span> committees c on<\/span> c.name = m.name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +-----------+--------+--------------+-----------+\n| member_id | member | committee_id | committee |\n+-----------+--------+--------------+-----------+\n|         1 | John   |            1 | John      |\n|         3 | Mary   |            2 | Mary      |\n|         5 | Amelia |            3 | Amelia    |\n|      NULL | NULL   |            4 | Joe       |\n+-----------+--------+--------------+-----------+\n4 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    This Venn diagram illustrates the right join:<\/p>\n\n\n\n
    \"mysql<\/figure>\n\n\n\n
    The following statement uses the right join clause with the USING<\/code> syntax:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nRIGHT<\/span> JOIN<\/span> committees c USING<\/span>(name<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    To find the committee members who are not in the members<\/code> table, you use this query:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nRIGHT<\/span> JOIN<\/span> committees c USING<\/span>(name<\/span>)\nWHERE<\/span> m.member_id IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +-----------+--------+--------------+-----------+\n| member_id | member | committee_id | committee |\n+-----------+--------+--------------+-----------+\n|      NULL | NULL   |            4 | Joe       |\n+-----------+--------+--------------+-----------+\n1 row in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    This Venn diagram illustrates how to use the right join to select data that exists only in the right table:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    MySQL CROSS JOIN clause<\/h2>\n\n\n\n
    Unlike the inner join<\/a>, left join<\/a>, and right join<\/a>, the cross join<\/a> clause does not have a join condition. <\/p>\n\n\n\n
    The cross join makes a Cartesian product<\/a> of rows from the joined tables. The cross join combines each row from the first table with every row from the right table to make the result set.<\/p>\n\n\n\n
    Suppose the first table has n<\/code> rows and the second table has m<\/code> rows. The cross-join that joins the tables will return nxm<\/code> rows.<\/p>\n\n\n\n
    The following shows the syntax of the cross-join clause:<\/p>\n\n\n
    SELECT<\/span> select_list\nFROM<\/span> table_1\nCROSS<\/span> JOIN<\/span> table_2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    This example uses the cross join clause to join the members<\/code> with the committees<\/code> tables:<\/p>\n\n\n
    SELECT<\/span> \n    m.member_id, \n    m.name AS<\/span> member<\/span>, \n    c.committee_id, \n    c.name AS<\/span> committee\nFROM<\/span>\n    members m\nCROSS<\/span> JOIN<\/span> committees c;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
    +-----------+--------+--------------+-----------+\n| member_id | member | committee_id | committee |\n+-----------+--------+--------------+-----------+\n|         1 | John   |            4 | Joe       |\n|         1 | John   |            3 | Amelia    |\n|         1 | John   |            2 | Mary      |\n|         1 | John   |            1 | John      |\n|         2 | Jane   |            4 | Joe       |\n|         2 | Jane   |            3 | Amelia    |\n|         2 | Jane   |            2 | Mary      |\n|         2 | Jane   |            1 | John      |\n|         3 | Mary   |            4 | Joe       |\n|         3 | Mary   |            3 | Amelia    |\n|         3 | Mary   |            2 | Mary      |\n|         3 | Mary   |            1 | John      |\n|         4 | David  |            4 | Joe       |\n|         4 | David  |            3 | Amelia    |\n|         4 | David  |            2 | Mary      |\n|         4 | David  |            1 | John      |\n|         5 | Amelia |            4 | Joe       |\n|         5 | Amelia |            3 | Amelia    |\n|         5 | Amelia |            2 | Mary      |\n|         5 | Amelia |            1 | John      |\n+-----------+--------+--------------+-----------+\n20 rows in set (0.00 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
    The cross join is useful for generating planning data. For example, you can carry the sales planning by using the cross join of customers, products, and years.<\/p>\n\n\n\n
    In this tutorial, you have learned various MySQL join statements, including cross join, inner join, left join, and right join, to query data from two tables.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
    To find rows in the right table that do not have corresponding rows in the left table, you also use a WHERE<\/a><\/code> clause with the IS NULL<\/a><\/code> operator:<\/p>\n\n\n
    To find members who are not the committee members, you add a WHERE<\/a><\/code> clause and IS NULL<\/a><\/code> operator as follows:<\/p>\n\n\n
    The inner join<\/a> clause joins two tables based on a condition which is known as a join predicate.<\/p>\n\n\n\n
    Third, query data<\/a> from the tables members<\/code> and committees<\/code>:<\/p>\n\n\n
    Second, insert<\/a> some rows into the tables members<\/code> and committees<\/code> :<\/p>\n\n\n