SQL Basic Learning _06_ set operations and joins

Set operation

1. Unions: UNION

? ? Cases:

? ? SELECT shohin_id, Shohin_mei
? ? From Shohin
? ? UNION
? ? SELECT shohin_id, Shohin_mei
? ? From Shohin2;

? ? The Shohin and Shohin2 two tables of shohin_id and Shohin_mei two were set up;

? ? Note that when the union operation, the number of columns must be equal and the data type of each column should be the same

? ? Keep Duplicate rows: UNION all

2. Intersection: INTERSECT

? ? The syntax is exactly the same as the Union, but it takes the intersection part

3. Difference set: EXCEPT

? ? The syntax is exactly the same as the Union, taking a record in the first table that is not in the second table

junction of Table

The set operation changes the number of records (number of rows), and the junction is to join the columns of the two tables, changing the number of columns

1. Inner coupling: INNER JOIN

? ? Cases:

? ? SELECT ts.tenpo_id, Ts.tenpo_mei, ts.shohin_id, S.shohin_mei, S.hanbai_tanka
? ? From Tenposhohin as TS INNER JOIN Shohin as S
? ? On ts.shohin_id = s.shohin_id

? ? The SELECT clause specifies the columns that need to be connected, noting that the from alias is valid in select because the select operation time is after from.

? ? The FROM clause specifies the names of two tables and joins with the inner join;

? ? The ON clause specifies an expression that, as a bridge of the junction, joins the corresponding column in the two tables by the record that satisfies the expression;

? ? Inner joins can be deleted using SELECT statements such as WHERE clauses;

2. Outer coupling: OUTER JOIN

? ? Outer joins show records that appear only in the main table compared to the inner junction;

? ? Cases:

? ? SELECT ts.tenpo_id, Ts.tenpo_mei, s.shohin_id, S.shohin_mei, S.hanbai_tanka
? ? From Tenposhohin as TS right OUTER JOIN Shohin as S
? ? on ts.shohin_id = s.shohin_id;

? ? The syntax and inner joins of outer joins are basically the same, but the outer joins use left and right to specify the main table.

? ? When using left, the table that is written on the right side is the main table, and the table that is written on the side is the main table.

3. Connecting three sheets or more

? ? First, the two tables are joined together, and then the two tables are connected to each other.

4. Crossover Join: Cross joins

? ? When cross-joins, each record of the left and right tables is connected to produce a long table; (Cartesian product)

?

? ?

?

SQL Basic Learning _06_ set operations and joins