Mysql query. What is the difference between Join and a SubQuery?

Question

I always though join gets the results of something and then joins only using those results.

SELECT * FROM tbl AS t1
JOIN tbl2 AS t2 ON t1.id = t2.foreignId 
JOIN tbl3 AS t3 ON t2.id = t3.foreignId
WHERE t1.date > SOMEDATE

From my understanding it will run the where statement and get only the results that fall within the date range. Then it will go through all of t2 and only try to match ids that connect to t1 (which may make the results smaller). Then with this smaller potential results it will do the same for T3 and output the final results.

But it appears that is NOT how this works? And the tables multiply instead of getting smaller. And apparently what i am describing above is more like subqueries? (NOTE that i said LIKE. I dont know how subquerys work)

How does JOIN work and what is the difference between a JOIN and a subquery?

I use both MySql and SQLite. I dont know if thats relevant.

Answer 1

sub query example

SELECT * 
FROM (
    SELECT * FROM tbl1 WHERE date > SOMEDATE
)
AS t1
JOIN tbl2 AS t2 ON t1.id = t2.foreignId 
JOIN tbl3 AS t3 ON t2.id = t3.foreignId

How the db engine performs is really up to its optimizer. Try putting an EXPLAIN in front of the query to see what the db engine is doing. Many variables come into consideration including index, table size, etc.

Answer 2

Joins and subqueries, in most cases, are the same. Most good optimizers will make this more of a syntaxual preference than anything else. Subqueries can lend for more flexability on occasion though, depending on your needs.

As far your example not working how you want, there are different kinds of JOINs. The JOIN you are using is known as an INNER JOIN. An INNER JOIN "tries" to link records together, but will return all unique results regardless of whether the join requirements are met.

If the result you want is to only get the dates from table 1 that meet the date requirements and then join them with the other tables you want a LEFT JOIN. LEFT JOIN means that the data from any JOINING tables MUST meet the joining requirements (from left to right [meaning from the first table to the last]) in order to be used.

SELECT * FROM tbl AS t1
LEFT JOIN tbl2 AS t2 ON t1.id = t2.foreignId
LEFT JOIN tbl3 AS t3 ON t2.id = t3.foreignId
WHERE t1.date > SOMEDATE

Answer 3

If the table used in the subquery returns a value twice, a JOIN will also return the matching rows twice, while an IN or EXISTS condition will return them only once.

JOINs tend to have better performance, but there are situations where that might not be the case, particular to each database (including version).

Reference:

• IN vs. JOIN vs. EXISTS