What is the default JOIN type in MySQL and how does it differ from LEFT JOIN?

Understanding JOIN vs LEFT JOIN in MySQL

MySQL's JOIN operations allow us to combine data from multiple tables by specifying common columns between them. However, it's crucial to understand the differences between various join types, particularly INNER JOIN and LEFT JOIN.

Consider the following cross-database query:

SELECT
            `DM_Server`.`Jobs`.*,
            `DM_Server`.servers.Description AS server,
            digital_inventory.params,
            products.products_id,
            products.products_pdfupload,
            customers.customers_firstname,
            customers.customers_lastname
        FROM `DM_Server`.`Jobs`
        INNER JOIN `DM_Server`.servers ON servers.ServerID = Jobs.Jobs_ServerID
        JOIN `cpod_live`.`digital_inventory` ON digital_inventory.jobname = Jobs.Jobs_Name
        JOIN `cpod_live`.`products` ON products.products_pdfupload = CONCAT(digital_inventory.jobname, ".pdf")
        JOIN `cpod_live`.`customers` ON customers.customers_id = products.cID
        ORDER BY `DM_Server`.`Jobs`.Jobs_StartTime DESC LIMIT 50

Query Execution Error with LEFT JOIN

When you attempt to convert all INNER JOINs to LEFT JOINs in the query, you may encounter an error. This is because, by default, MySQL uses INNER JOIN when no explicit join type is specified. LEFT JOIN must be explicitly specified using the "LEFT JOIN" keyword.

Default Join Type

Contrary to popular belief, the default join type in MySQL is not LEFT JOIN but INNER JOIN. Inner joins only return rows that have matching values in the join columns across all tables involved in the query.

Visual Representation of Joins

To better understand the difference between INNER JOIN and LEFT JOIN, consider the following visual representations:

• INNER JOIN:

Table 1 | Column A | Column B
--------+---------+---------
1       | A1       | B1
2       | A2       | B2

Table 2 | Column C | Column D
--------+---------+---------
1       | C1       | D1
2       | C2       | D2

Result:

| Column A | Column B | Column C | Column D |
|---|---|---|---|
| A1       | B1       | C1       | D1

• LEFT JOIN:

Table 1 | Column A | Column B
--------+---------+---------
1       | A1       | B1
2       | A2       | B2

Table 2 | Column C | Column D
--------+---------+---------
1       | C1       | D1

Result:

| Column A | Column B | Column C | Column D |
|---|---|---|---|
| A1       | B1       | C1       | D1
| A2       | B2       | NULL     | NULL

As you can see, LEFT JOIN includes all rows from the left table (Table 1) and matches them with rows from the right table (Table 2) when there are matches. If there are no matches, NULL values are included for the missing columns.

The above is the detailed content of What is the default JOIN type in MySQL and how does it differ from LEFT JOIN?. For more information, please follow other related articles on the PHP Chinese website!