Chomper Stomping jQuery/JavaScript/CSS 3/HTML 5, Java/PHP/Python/ActionScript, Git, Chrome/Firefox Extensions, Wordpress/Game/iPhone App Development and other random techie tidbits I've collected

22May/080

Inner Join and Left Join in MySQL

I've been using query after query written by someone else in the code I'm maintaining recently. It's as if they've never heard of a JOIN. They continually do:

SELECT *

FROM table1, table2

WHERE table1.col = table2.col

I came from an Oracle background, and I'm not even sure if that query would run in Oracle. At least, before now I'd never really seen anything like that. So I finally set out to figure out what it was actually doing. I assumed it was doing a LEFT JOIN, but was surprised initially to find out it was doing an INNER JOIN (although, when you think about it, that makes sense).

Apparently, there are two separate ways you can do an INNER JOIN in MySQL. First, and maybe most common for people introduced to Databases through MySQL, is the way I showed above. For those of you who like to explicitly clearly state exactly what you are intending to happen, there is the following:

SELECT *

FROM table1

INNER JOIN table2

ON table1.col = table2.col

Both do the exact same thing. What I initially thought it was doing was:

SELECT *

FROM table1

LEFT JOIN table2

ON table1.col = table2.col

Which is not the same at all. Well, the results from INNER JOIN are a subset of the results of LEFT JOIN, but that's the only similarity.

LEFT JOIN returns every result from the first table no matter what. It also returns every result from the second table found by the ON clause:

INNER JOIN only returns the results found by the ON clause.

Note the difference in the number of returned results by each query. The difference here is actually very small, but in some cases it can be much much more pronounced.

There's tons of astonishingly better explanations of this that you could read. This post is just an exploration in to what JOIN is used when you don't actually specify a JOIN in MySQL for my own note-keeping purposes...

Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.