MySQL: Quick breakdown of the types of joins

mysql join
Bryan Field picture Bryan Field · Jun 9, 2011 · Viewed 138.5k times · Source

I would like a quick breakdown of the types of MySQL joins. I know of these, the rest I am not sure what they mean.

  • comma separated (what exactly is this short for?): SELECT * FROM a, b WHERE b.id = a.beeId AND ...
  • show information from a, even if there are no matches in b: SELECT * FROM a LEFT OUTER JOIN b ON b.id = a.beeId WHERE ...

I have seen other joins, but want to know what makes them different, what is INNER/OUTER, does adding LEFT change things.

I already know how joins work, I just want to know if there are other types of joins, or if they are just different ways to get the same result.

Answer

Ryan picture Ryan · Jun 9, 2011

Based on your comment, simple definitions of each is best found at W3Schools The first line of each type gives a brief explanation of the join type

  • JOIN: Return rows when there is at least one match in both tables
  • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
  • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
  • FULL JOIN: Return rows when there is a match in one of the tables

END EDIT

In a nutshell, the comma separated example you gave of 

SELECT * FROM a, b WHERE b.id = a.beeId AND ...

is selecting every record from tables a and b with the commas separating the tables, this can be used also in columns like

SELECT a.beeName,b.* FROM a, b WHERE b.id = a.beeId AND ...

It is then getting the instructed information in the row where the b.id column and a.beeId column have a match in your example. So in your example it will get all information from tables a and b where the b.id equals a.beeId. In my example it will get all of the information from the b table and only information from the a.beeName column when the b.id equals the a.beeId. Note that there is an AND clause also, this will help to refine your results.

For some simple tutorials and explanations on mySQL joins and left joins have a look at Tizag's mySQL tutorials. You can also check out Keith J. Brown's website for more information on joins that is quite good also.

I hope this helps you

Related questions

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More
How to do a FULL OUTER JOIN in MySQL?

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

Read More
INNER JOIN ON vs WHERE clause

For simplicity, assume all relevant fields are NOT NULL. You can do: SELECT table1.this, table2.that, table2.somethingelse FROM table1, table2 WHERE table1.foreignkey = table2.primarykey AND (some other conditions) Or else: SELECT table1.this, table2.that, table2.somethingelse …

Read More