Database Operations (III)

1. Internal connection: INNER JOIN

Basic syntax: Select a. column name from table 1 a INNER JOIN table 2 B on a.ID =b.id [and ... inner join table 3 C on ...] where a.name = ' value ';

2. External connection:

(1) Left outer connection: Ieft Join

Select a. column name from table 1 a LEFT JOIN table 2 B on a.ID =b.id and b. Column name = ' value ' [INNER JOIN table 3 C on ...] where a.name = ' value ';

(2) Right outer connection: Starboard Join

Select a. column name from table 1 a right join table 2 B on a.ID =b.id and b. Column name = ' value ' [INNER JOIN table 3 C on ...] where a.name = ' value ';

Note: The and post content is related only to table 2

3. Merging result sets

(1) de-Consolidation: Union

Select Column_names from table 1 union select column_names from table 2;

Note: Table 1 and Table 2 must: the same number of columns, the same order of columns, the same type of columns

Table 1 and Table 2 can: Different column names, directly add data (select 5 as Column name s from table name)

When the column appears aliased: Select Column_names A From table 1 union select Column_names b from table 2;

The output is only related to a

　　　　　　　　　　　　

(2) Full merger: UNION ALL

Select Column_names A From Table 1 union ALL select Column_names B from table 2 order by column name;

Note: Order BY is written in the last

4. Except: Remove the same data as table 1 that exists in table 2

Select Column_names A From table 1 except select Column_names b from table 2;

Intersect: Take intersection

Select Column_names A From table 1 intersect select Column_names b from table 2;

Database Operations (III)