Difference in MySQL JOIN vs LEFT JOIN

Question

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 JOINs. I thought that by not specifying a type of join it was assumed to be a LEFT JOIN. Is this not the case?

Mark Byers · Accepted Answer · 2012-03-19 12:53:05Z

203

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

enter image description here

Left join

enter image description here

edited Mar 19, 2012 at 12:53

answered Mar 19, 2012 at 12:41

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user254875486 Over a year ago

By Cross-database the TS probably means that his query uses tables from multiple databases.

Ben Over a year ago

Is there a big reason why a LEFT JOIN would be so much slower than an INNER JOIN ?

Mark Byers Over a year ago

@Webnet: It could potentially return more rows. That could, in some situations, cause it to be slower.

Michael Durrant · Accepted Answer · 2012-03-19 12:47:12Z

14

No. When a type isn't specified, an INNER JOIN is used. To read up on differences; wikipedia

edited Mar 19, 2012 at 12:47

Michael Durrant

97.1k102 gold badges352 silver badges536 bronze badges

answered Mar 19, 2012 at 12:42

user254875486

11.3k7 gold badges39 silver badges65 bronze badges

Comments

Flash · Accepted Answer · 2012-03-19 12:42:49Z

11

I believe the default is INNER JOIN if you just specify JOIN.

answered Mar 19, 2012 at 12:42

Flash

16.9k15 gold badges74 silver badges100 bronze badges

Comments

Java · Accepted Answer · 2012-03-19 14:07:21Z

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.

Collectives™ on Stack Overflow

Difference in MySQL JOIN vs LEFT JOIN

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Related