8-3 Frequently Asked Questions for Writing SQL Statements

1. Focus on test sites

SQLThe statement focuses on investigating various common connections
1. Inner join ( INNER JOIN): Matching rows will only be returned when there is a match between the two tables
2. Outer join ( LEFT/RIGHT JOIN): Return rows of one table, even if the other does not match
3. All Connect ( FULL JOIN): return as long as there is a match in a certain table

Two, inner connection

INNER JOIN
1. Connect the data that can be associated with the left table and the right table and return it
2. Similar to finding the "intersection" of the two tables
3.select * from A inner join B on a.id=b.id;

Sample tables are
used A, Bthese two tables are used as examples.

Insert picture description here


Let’s write the inner join first ( inner join):

select A.id as a_id, B.id as b_id, A.val as a_val, B.val as b_val from A inner join B on A.id = B.id;

operation result:

Insert picture description here

Three, external connection

Outer join contains left join and right join
1. Left join returns all records in the left table, even if there is no matching record in the
right table 2. Right join returns all records in the right table, even if there is no matching record in the left table
3. No match the fields provided NULL
Mysqlfor use left joinand right joinimplemented

Left connection result:

select A.id as a_id, B.id as b_id, A.val as a_val, B.val as b_val from A left join B on A.id = B.id;
Insert picture description here


Right connection result:

select A.id as a_id, B.id as b_id, A.val as a_val, B.val as b_val from A right join B on A.id = B.id;
Insert picture description here

Four, fully connected

FULL OUTER JOIN
1. As soon as one table there is a match, return line
2. The sum of two similar tables "and set"
3 but Mysqldoes not support, may be used left join, union, right joincombined analog

select A.id as a_id, B.id as b_id, A.val as a_val, B.val as b_val from A left join B on A.id = B.id union select A.id as a_id, B.id as b_id, A.val as a_val, B.val as b_val from A right join B on A.id = B.id;
Insert picture description here