Study database notes 7. Database notes
Internal Connection Query
The two tables are connected to one table using inner join, and then queried. When filtering the two tables, the on (where is the condition for querying a single table) is used)
Example
Select boy. hid, bname, girl. hid, gname # Check the records of the two tables.
From
Boy inner join girl # boy table join girl table
On # filter on
Boy. hid = girl. hid; # The homeid of the boy is the same as that of the girl.
Syntax: select xxx from table1 inner join table2 on table1.xx = table2.xx; Use inner join to bypass and filter by the condition after on
Left and right connections
You can learn one and the other.
Or the example just now
Select boy. hid, bname, girl. hid, gname
From
Boy left join girl # I just changed the inner here to left, which means that the boy table is the main one. Not only does the on condition match but does not match, as long as the boy table is also retrieved, the condition is NULL.
On
Boy. hid = girl. hid;
The difference between the left table and the right table mainly refers to this SQL statement: the boy table on the left of the boy left join girl table and the girl table on the right
For the right join, you only need to change left to right to change the dominant relationship between the two tables and to the right table.
Select boy. hid, bname, girl. hid, gname
From
Boy right join girl
On
Boy. hid = girl. hid;
Relationship between Cartesian Product and Table query