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-19T12:53:05.543

200

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 '12 at 12:53

answered Mar 19 '12 at 12:41

Mark Byers

811,555
193
1,581
1,452

By Cross-database the TS probably means that his query uses tables from multiple databases. – user254875486 Mar 19 '12 at 12:47
2

Is there a big reason why a `LEFT JOIN` would be so much slower than an `INNER JOIN` ? – Ben Mar 19 '12 at 12:55
4

@Webnet: It could potentially return more rows. That could, in some situations, cause it to be slower. – Mark Byers Mar 19 '12 at 12:56

score 14 · Answer 2 · edited Mar 19 '12 at 12:47

14

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

edited Mar 19 '12 at 12:47

Michael Durrant

93,410
97
333
497

answered Mar 19 '12 at 12:42

user254875486

11,190
7
36
65

score 11 · Answer 3 · answered Mar 19 '12 at 12:42

11

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

answered Mar 19 '12 at 12:42

Flash

15,945
13
70
98

Java · Answer 4 · 2012-03-19T14:07:21.360

9

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.

edited Mar 19 '12 at 14:07

answered Mar 19 '12 at 14:00

Java

2,451
10
48
85

Difference in MySQL JOIN vs LEFT JOIN

4 Answers4

Linked