Summary of sql connection

Shulou Source: shulou.com Published: 2022-06-01 13:23:06 10月01日 Update

1. Internal connection inner join

1.1 Cross-connect cross join

SELECT * FROM person_hobby,person,hobby

Cartesian product of table A (m column), table B (n column), table C (l column) (m*n*l column)

1.2 equivalent connection

SELECT * FROM person as pjr hobby as hjr personalize hobby as ph where p.person_id=ph.person_id and h.hobby_id=ph.hobby_id

Select according to conditions on the basis of cross-connections (filter out rows with illegal conditions)

1.3 Natural connection natural join

SELECT * FROM person natural join person_hobby natural join hobby

Remove duplicate columns on the basis of equivalent joins (if A _ Magi B has a column with the same name, otherwise it is equivalent to a cross-join)

two。 External connection outter join

2.1 left external connection left join

SELECT * FROM person_hobby right join person on person_hobby.person_id=person.person_id left join hobby on person_hobby.hobby_id=hobby.hobby_id

All rows of the left table and the right table correspond to the rows of the left table

2.2 right external connection right join

SELECT * FROM person_hobby right join person on person_hobby.person_id=person.person_id right join hobby on person_hobby.hobby_id=hobby.hobby_id

All rows of the right table and left table correspond to the rows of the right table

2.3 full external connection full join (supported by some database management systems, not supported by mysql)

Select * from A full join B on A.a=B.a

3. Union union: merges multiple query results to remove duplicate rows (union all contains duplicate rows)

Select... From A where... Union select... from b where... Union select... From c where... Group by... Order by...

Usage rules:

Each select statement needs to select the same number of columns with compatible data types.

Group by or order by can only be used for the last time, not every select, because union is grouped or sorted after merging.

4.intersect: finding the intersection

Select... From A where... Intersect select... from b where... Intersect select... From c where...

5.except: subtraction set

Select... From A where... Except select... from b where... Except select... From c where...