MySQL: Quick breakdown of the types of joins [duplicate]

by

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

More Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. How can I do three table JOINs in an UPDATE query?
  3. 1052: Column ‘id’ in field list is ambiguous
  4. MySQL update table based on another tables value
  5. MySQL join with where clause
  6. How can I join multiple SQL tables using the IDs?
  7. JOIN queries vs multiple queries
  8. Why does MySQL report a syntax error on FULL OUTER JOIN?
  9. conditional join in mysql
  10. MYSQL Select MAX Date inside a join statement
  11. How to use mysql JOIN without ON condition?
  12. Mixing ANSI 1992 JOINs and COMMAs in a query
  13. a count for each join – optimisation
  14. Combine Multiple child rows into one row MYSQL
  15. Cumulative sum over a set of rows in mysql
  16. MySql Count cannot show 0 values
  17. MySQL update a joined table
  18. MySQL join query using like?
  19. MySQL JOIN with LIMIT 1 on joined table
  20. Can I resolve this with pure mysql? (joining on ‘;’ separated values in a column)
  21. Laravel join with 3 Tables
  22. MySQL Insert & Joins
  23. mySQL – Create a New Table Using Data and Columns from Three Tables
  24. Count with IF condition in MySQL query
  25. How to join two tables using a comma-separated-list in the join field
  26. Mysql select where not in table
  27. When to use a left outer join?
  28. In what order are MySQL JOINs evaluated?
  29. MySQL cartesian product between two SELECT statements
  30. MySQL Multiple Joins in one query?

Leave a Comment