How JOIN works internally in MySQL?

Question

Here is my test script, there is no primary key as ID, test case is to join multiple rows having same key, and result in how many rows.

create table Test1(id integer, name varchar(100));
insert into Test1(id, name) values(1, "Vijay");
insert into Test1(id, name) values(1, "Sandy");
insert into Test1(id, name) values(1, "Rohit");

create table Test2(id integer, surname varchar(100));
insert into Test2(id, surname) values(1, "karma");
insert into Test2(id, surname) values(1, "sharma");
insert into Test2(id, surname) values(1, "yadav");

select Test1.id , Test1.name , Test2.surname from Test1 
left outer join
Test2 on Test1.id = Test2.id;


1   Vijay   karma
1   Sandy   karma
1   Rohit   karma
1   Vijay   sharma
1   Sandy   sharma
1   Rohit   sharma
1   Vijay   yadav
1   Sandy   yadav
1   Rohit   yadav

select Test1.id , Test1.name , Test2.surname from Test1 
right outer join
Test2 on Test1.id = Test2.id;

1   Vijay   karma
1   Vijay   sharma
1   Vijay   yadav
1   Sandy   karma
1   Sandy   sharma
1   Sandy   yadav
1   Rohit   karma
1   Rohit   sharma
1   Rohit   yadav

How this works internally? Does is keep one table static and streams another table on it, and how pointers works in such cases and where the metadata gets stored while join process.

In terms of relational databases, if you don't have a PRIMARY KEY, then you don't really have a table — Strawberry, Feb 17 '20 at 12:31

The Impaler · Answer 1 · 2020-02-17T14:06:02.437

How this works internally? Does is keep one table static and streams another table on it, and how pointers works in such cases and where the metadata gets stored while join process.

Relational database engines perform operations on the data in order to access, update, or delete it. Each engine has a smaller or wider repertoire of operations implemented in it.

For example, in your case, the MySQL engine may work as shown below:

Since there's no filtering predicate (aka WHERE clause) it may execute a "Full Table Scan" on one table, shown as "all" in the execution plan.
Then, it could access the related table using an "Index Range Scan", known as "index" in MySQL's lingo.

How are these operations decided? That's the job for the SQL planner and SQL optimizer. They read your SQL query and produce a tree of operations that will be carried out to produce the result you want. This tree may change in time according to MySQL's heuristics, and changes in the data.

How do you get the specific plan? Just get the plan using:

explain <query>

You'll see the query plan in all its glory.

How JOIN works internally in MySQL?

1 Answers1