1. Equivalent connection
Principle: form a large logical table with multiple single tables.
Grammar:
select * from Table A, table B where table A. primary key = table B. foreign key and query condition
select * from Table A, table B where Table A. primary key = table B. foreign key and C. primary key =B. foreign key and query condition
Two. Internal connection
First judge, then connect.
The result of internal connection is the same as the result of equivalent connection.
Syntax: two tables
select *
from Table A inner join table B
on Table A. primary key = table B. primary key
where query criteria
Syntax: three tables
select *
from Table A inner join table B
on Table A. primary key = table B. primary key
inner join Table C
on Table C. primary key = table B. primary key
where query criteria
Example:
1） Internal connection
select a.*,b.* from a inner join b on a.id=b.parent_id
The result is
1 Zhang 31231
2 Lee four 2342
select a.*,b.* from a inner join b on a.id=b.parent_id
The result is
1 Zhang 31231
2 Lee four 2342
Three, nested queries (subqueries)
Principle: the SQL statements of multiple single table queries are stitching together.
Single table query results
Single values: =,, = =, > > =, < < = =
Multiple values: set in, not in
select *
from Table name
where Field in (select * from Table name
where Field = ‘* *’) and Field 2 =(
select * from Table name
where Field = ‘* *’)
Example:
————————————————-
aTable ID name B table ID job parent_id
1 Zhang 31231
2 Lee four 2342
3 Wang Wu 3344
a.idRelationship with parent_id
————————————————-
aTable ID name B table ID job parent_id
1 Zhang 31231
2 Lee four 2342
3 Wang Wu 3344
a.idRelationship with parent_id
Four, left lateral connection
LEFT JOIN Or LEFT OUTER JOIN
select a.*,b.* from a left join b on a.id=b.parent_id
The result is1 Zhang 312312 Lee four 23423 Wang Wu null
The result set of the left-out join includes all the rows of the left table specified in the LEFT OUTER clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all the selection list columns in the right table in the associated result set row are null.
Five. Right outside connection.
RIGHT JOIN Or RIGHT OUTER JOIN
select a.*,b.* from a right join b on a.id=b.parent_id
The result is1 Zhang 312312 Lee four 2342null 3 34 4
The right outer join is the reverse join from left to outside. All rows of the right table will be returned. If a row in the right table does not match rows in the left table, it returns null values for the left table.。
Six. Complete connection
FULL JOIN Or FULL OUTER JOIN
select a.*,b.* from a full join b on a.id=b.parent_id
The result is1 Zhang 312312 Lee four 2342null 3 34 43 Wang Wu null
The complete external join returns all rows in the left table and the right table. When a row does not match rows in another table, the list of columns in another table contains null values. If there is a matching row between the tables, the entire result set contains the data value of the base table.