Difference in MySQL JOIN vs LEFT JOIN
MysqlQuery OptimizationLeft JoinInner JoinMysql Problem Overview
I have this 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
it runs fine until I make them LEFT JOIN
s. I thought that by not specifying a type of join it was assumed to be a LEFT JOIN
. Is this not the case?
Mysql Solutions
Solution 1 - Mysql
> I thought that by not specifying a type of join it was assumed to be a LEFT JOIN. Is this not the case?
No, the default join is an INNER JOIN.
Here is a visual explanation of SQL joins.
Inner join
Left join
Solution 2 - Mysql
No. When a type isn't specified, an INNER JOIN
is used. To read up on differences; wikipedia
Solution 3 - Mysql
I believe the default is INNER JOIN
if you just specify JOIN
.
Solution 4 - Mysql
> If you just mentioned JOIN in query by default it will be considered > as a INNER JOIN.
Left join:Left join will take all the elements from Left table and only matching records from the Right table as Follows. example:
SELECT column_name(s)
FROM table_name1 #(Left table)
LEFT JOIN table_name2 #(Right table)
ON table_name1.column_name=table_name2.column_name
Hope this helps.