The Perplexity about the underlying algorithm of left join

have a question about SQL:
about the left join implementation of PostgreSQL or MySQL

suppose: table structure

table_name :user
uid    name
1      
2      
3      

table_name : age
uid    age
1      10
2      20
3      30
4      40

SQL:

1:
SELECT t1.user,t2.age
FROM user t1
LEFT JOIN age t2 ON t1.uid=t2.uid

2:
SELECT t1.user
       ,(SELECT age FROM age WHERE uid=t1.uid) as age
FROM user t1

:21........unbelievable

skepticism:

1SQL:
    :
    1. user
    2. age
    3. 

    :
    1. user
    2. userage
    3. 



~

add:

Postgresql mysql join left-join

Apr.07,2021

the execution process of left join:
1.From: perform the Cartesian product on the left and right tables to produce the first table vt1 (temporary table). The number of rows for the ON (n is the number of rows of the left table, m is the number of rows of the right table
2.ON: according to the conditions of ON line-by-line filter vt1, to insert the results into the vt2
3.JOIN: add an external row, if you specify LEFT JOIN, then first traverse each row of the left table, in which the row not in vt2 will be inserted into the vt2, row the remaining fields will be filled with NULL, to form vt3; if RIGHT JOIN is specified. However, if INNER JOIN, is specified, no external line will be added, and the above insertion process is ignored. Vt2=vt3 (so the Filter condition of INNER JOIN is executed in ON or WHERE, the result is no different, which will be described in more detail below)
4.Whree: conditional Filter on vt3, and rows that meet the condition are output to vt4
5.Select: fetch the specified field of vt4 to vt5

from the Left join process, you will find that the steps are a little more complex than 2, which may cause some time-consuming

depends on the scene. The data volume of thousands of, hash join details is faster than that of full table scanning

Previous: How to get the await return value

Next: Php regular writing