What actually happens during the JOINs table?

Question

I am trying to verify my understanding of JOINs .

For the following query:

 SELECT * FROM tableA join tableB on tableA.someId = tableB.someId join tableC on tableA.someId = tableC.someId; 

Does RDMS execute a similar pseudo code as follows:

 List tempResults for each A_record in tableA for each B_record in tableB if (A_record.someId = B_record.someId) tempResults.add(A_record) List results for each Temp_Record in tempResults for each C_record in tableC if (Temp_record.someId = C_record.someId) results.add(C_record) return results; 

Basically, the more records with the same someId tableA has tableB and tableC , the more records the RDMS scan has? If all 3 tables have entries with the same someId , then, essentially, a full table scan is performed on all three tables?

Do I understand correctly?

Answer 1

Each provider request processor, of course, is written (encoded) in a slightly different way, but they probably have many common methods. A join can be implemented in various ways, and which one is chosen in any vendor implementation will depend on the specific situation, but the factors that will be considered include whether the data will already be sorted by the join attribute, the relative number of records in each table (the connection between 20 records in one data set with a million records in another will be done differently than one, where each set of records is comparable in size). I do not know the internal components for MySQL, but there are three different connection methods for the SQL server: merge merge, Loop merge, and Hash merge. Check out this .