MySQL Learning Note (iii) JOIN usage

Database operation of separate additions and deletions, which query operations accounted for the system 90%, the optimization of the SQL statement is basically the optimization of query statements. Next, you'll learn the use of join, which includes: INNER join (inner join), left join (outer join), right join (off outer join), full join (fully outer join), cross join (crossed join). Before the instructions, create the user, Schoolmap, and school three tables for your experiment.

　　　　

　　　　

　　　　

1. INNER JOIN

1), INNER join returns rows in which the junction fields are equal in two tables, that is, the junction fields in two tables exist and are equal.

2), INNER the syntax for connecting two data tables:

SELECT * FROM Table a INNER JOIN table B on table A. field = table B. field;

3), Example:

　　　　

4), INNER join connection three data tables usage:

SELECT * FROM (table 1 INNER join table 2 on table 1. Field number = Table 2. Field number) INNER JOIN table 3 on table 1. Field number = Table 3. Field number

Connect multiple tables and so on

5), INNER join when the use of the condition is equivalent to full join without the on condition, cross JOIN

　　　　

　　　　

　　　　

6, INNER into the query after the condition plus on the back and where the effect is the same

　　　　

2. Left join (outer connection)

1), left join returns records that include all records in the table and the junction fields in the right table.

2), left join the syntax for connecting two data tables:

SELECT * FROM Table a left JOIN table B on table A. field = table B. field;

　　3), leftJoin example:

　　　　

4), left JOIN connection three data tables usage:

SELECT * FROM (table 1 left JOIN table 2 on table 1. Field number = Table 2. Field number) left JOIN Table 3 on table 1. Field number = Table 3. Field number

Connect multiple tables and so on

3. Right join (left outer connection)

1), right join returns records that include all records in the table on the left and the junction fields in the table on the bottom.

2), rightjoin syntax for connecting two data tables:

SELECT * FROM Table A right JOIN table B on table A. field = table B. field;

　　3), rightjoin example:

　　　　

4), right join connect three data Tables usage:

SELECT * FROM (table 1 Right Join table 2 on table 1. Field number = Table 2. Field number) Right Join Table 3 on table 1. Field number = Table 3. Field number

Connect multiple tables and so on

4. Full JOIN

MYSQL does not support full join through the left join and right join to implement

Usage:

SELECT * from A LEFT join B on a.id = b.id (where condition)
Union
SELECT * from A right join B on a.id = b.id (where condition);

MySQL Learning Note (iii) JOIN usage