Mysql query. What is the difference between Join a

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

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

Answer 2

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.