This is the sixth day of my participation in the August Text Challenge.More challenges in August

Join queries

  • Join query is a join that records multiple tables and assembs data according to a specified condition

  • Join query results may change the number of records, the number of fields will definitely increase

  • Join query meaning: When the user views data, the data needs to be displayed from multiple tables

  • Join query categories: inner join, outer join, natural join, cross join

Cross connection

  • Cross join: iterate over each entry in a table, and each entry will be matched in another table. Because there is no matching condition, the matching result will be retained, and eventually the join itself will be increased (retained). The result is cartesian product

  • The basic grammar

    • The left table cross join the right table
    • Select * from left table; select * from right table
    select * from my_pri1 cross join t2;

select * from my_pri1,t2;
Copy the code

In the connection

  • Inner join: Select each entry from the left table and match it with all entries in the right table. The matching condition must be certain. The matching condition must be the same in the left table and the right table
  • The basic grammar
    • [inner] join right table on left table. Field = right table. field
    • On represents the connection condition, and the condition bullet represents the same business meaning that is when left table. Field = right table. The result is retained only when
select * from my_pri1 inner join t2 on my_pri1.pri_id = t2.id;

-- short form, use join, omit inner
select * from my_pri1  join t2 on my_pri1.pri_id = t2.id;

-- Use alias
select * from my_pri1 as p join t2 as t on p.pri_id = t.id;

- omitted the as
select * from my_pri1  p join t2  t on p.pri_id = t.id;
Copy the code
  • If an inner join is not queried using the condition after ON, it is equivalent to a cross join and the result is also a Cartesian product
  • Internal joins can use where instead of on, but where is not as efficient as ON
select * from my_pri1 p join t2 t where p.pri_id = t.id;
Copy the code

Outer join

  • Select * from outer join; select * from outer join; select * from outer join; select * from outer join; select * from outer join; select * from outer join; select * from outer join; select * from outer join

  • Outer connection classification

    • Left join: left outer join (left join). The left table is the primary table
    • Right Join: the right outer join (right join), with the right table as the primary table
    - left connection
select * from my_pri1 p left join t2 t on p.pri_id = t.id;

- the right connection
select * from my_pri1 p right join t2 t on p.pri_id = t.id;
Copy the code
    • Although there is a primary table difference between the left join and right join, the result is to display data from the left table on the left and data from the right table on the right.
    • Left join and right join can interrotate.

A natural connection

  • The system uses the field name as the matching mode, the field with the same name as the condition, and multiple fields with the same name as the condition

  • Natural connective classification

    • Natural join: left table natural join right table
    • A natural connection automatically uses a field with the same name as a connection condition, and a field with the same name is merged after the connection
    select * from my_pri1 natural join t2;
Copy the code
    • Natural left/right join the right table
    select * from my_pri1 natural left join t2;
Copy the code