Fun MySQL fact of the day: everything is a join
This week, we're going to start looking at table joins in MySQL, which, interestingly enough, is the only way MySQL executes queries.
When we think about joins, we often think of a query like
SELECT id FROM my_table t1 INNER JOIN my_join_table t2 ON t1.id = t2.my_table_id. And while that definitely is a query with a join, you probably wouldn't think of other joins like
SELECT id FROM my_table. But both queries are, in fact, joins. At least in MySQL, anyways. So, maybe you're thinking "I don't see a join in the second one. I think you've lost it", and maybe you're right about me losing it, but while there isn't an explicit join in the second query, it is a join nonetheless. We're going talk about joins over the next several days, but today, we're just going to reason out why, in MySQL, a simple select on a single table is a join.
If we stop and think about this and consider some simple-enough queries like those we've considered so far in Fun MySQL Facts Of The Day, after each row is read from MySQL, it is sent to the network. This is true when querying a single table just as well as when we're reading the result of a join between multiple tables (ignoring some technical details we'll talk about later). How does this make sense? Simply, in a single-table query, the single table is both the first table of a join and the last table of a join, with the end result of reading each row being "send to network". In the case of a join between two tables, the end result of reading a row in the first table is "look for matching rows in this next table", with the end result of each row in that table being "send to network".
So there you have it: all queries are joins.