What is the difference between JOIN and LEFT JOIN in MySQL?

Understanding the Difference between JOIN and LEFT JOIN in MySQL

In SQL, joining tables allows us to combine data from multiple tables based on specific criteria. Two common types of joins are JOIN and LEFT JOIN.

JOIN vs LEFT JOIN

JOIN returns only rows that match on the join condition, essentially creating a subset of records that meet the specified criteria. On the other hand, LEFT JOIN retrieves all rows from the left table and only those rows that have a match in the right table. Rows without matches in the right table are returned with NULL values.

Default Join Type in MySQL

Contrary to the user's assumption, the default join type in MySQL is INNER JOIN, which means that it only includes rows that satisfy the join condition. If you want to use a LEFT JOIN, you must explicitly specify it in your query.

Example

Consider the following example provided by the user:

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

By default, this query uses INNER JOINs, which only return jobs that have corresponding entries in all four joined tables. To convert it to a LEFT JOIN, you would change the JOIN keywords to LEFT JOIN, as follows:

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`
        LEFT JOIN `DM_Server`.servers ON servers.ServerID = Jobs.Jobs_ServerID
        LEFT JOIN `cpod_live`.`digital_inventory` ON digital_inventory.jobname = Jobs.Jobs_Name
        LEFT JOIN `cpod_live`.`products` ON products.products_pdfupload = CONCAT(digital_inventory.jobname, ".pdf")
        LIMIT 50

The above is the detailed content of What is the difference between JOIN and LEFT JOIN in MySQL?. For more information, please follow other related articles on the PHP Chinese website!