catalogue

Addition and subtraction of tables

What is set operation

Addition of tables -- UNION

Precautions for set operation

Set operation with duplicate rows - ALL option

Select the common part in the table - INTERSECT (intersection)

Recorded subtraction - Excel (difference set)

Join (join tables in columns)

What is connection

INNER JOIN

Inline key points ① - FROM clause

Inline key points ② - ON Clause

Inline key points ③ - SELECT clause

Inline joins are used in conjunction with the WHERE clause

External join

Key points of outreach ① - select all the information in a single table

Key points of outreach ② - is each table a master table?

Connect more than 3 tables

Addition and subtraction of tables

What is set operation

Set operation is the addition and subtraction of records that meet the same rule.

Use UNION (UNION), intersect (intersection), except (difference) and other set operators to perform set operations.

Set operations can remove duplicate rows.

If you want the set operator to keep duplicate rows, you need to use the ALL option.

Addition of tables -- UNION

Use UNION to add tables. Suppose there are tables Shohin and Shohin2

SELECT shohin_id, shohin_mei
FROM Shohin
UNION
SELECT shohin_id, shohin_mei
FROM Shohin2;

Precautions for set operation

1. Records as operands must have the same number of columns
2. The types of columns in records as operands must be consistent
3. You can use any SELECT statement, but the ORDER BY clause can only be used for the last time

Set operation with duplicate rows - ALL option

SELECT shohin_id, shohin_mei
FROM Shohin
UNION ALL
SELECT shohin_id, shohin_mei
FROM Shohin2;

Select the common part in the table - INTERSECT (intersection)

Use INTERSECT to select the common part of the table

SELECT shohin_id, shohin_mei
FROM Shohin
INTERSECT
SELECT shohin_id, shohin_mei
FROM Shohin2;

Recorded subtraction - Excel (difference set)

Subtract records with Excel

SELECT shohin_id, shohin_mei
FROM Shohin
EXCEPT
SELECT shohin_id, shohin_mei
FROM Shohin2;

Join (join tables in columns)

What is connection

Set operations such as UNION and INTERSECT are performed in the unit of row direction. Generally speaking, these set operations will lead to the increase or decrease of the number of record lines. Using UNION will increase the number of rows, and using INTERSECT or excel will reduce the number of rows.

Join operation is to add columns from other tables for column field operation.

INNER JOIN

Join the two tables internally

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;

Inline key points ① - FROM clause

The keyword INNER JOIN can join two tables together. Since the table name is too long, which will affect the readability of SQL statements, try to use aliases.

Inline key points ② - ON Clause

After ON, we can specify the columns (join keys) used for the join of the two tables. SHOHIN is used in this example_ id. That is, ON is specifically used to specify connection conditions. It can play the same role as WHERE. When you need to specify multiple keys, you can also use AND, OR. The ON clause is essential when inlining. AND ON must be written between FROM AND WHERE.

Inline key points ③ - SELECT clause

Use < table alias > in the SELECT clause< Column name > is written in this format.

Inline joins are used in conjunction with the WHERE clause

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
WHERE TS.tenpo_id = '000A';

External join

Outer join the two tables

SELECT TS.tenpo_id, TS.tenpo_mei,TS.shohin_id,S.shohin_mei,S.hanbai_tanka
FROM TenpoShohin AS TS OUTER JOIN Shohin AS s
	ON TS.shohin_id = S.shohin_id

Key points of outreach ① - select all the information in a single table

The inline union can only select data from two tables that exist at the same time,

As long as the data exists in a table, it can be read out

The origin of the name of an external join is also related to NULL. The so-called "external" is the information that does not exist in the external meta table

Key points of outreach ② - is each table a master table?

Specify the keywords LEFT and RIGHT of the main table. If LEFT is used, the table written on the LEFT in the FROM clause is the main table, and if RIGHT is used, the table on the RIGHT is the main table

SELECT TS.tenpo_id, TS.tenpo_mei,TS.shohin_id,S.shohin_mei,S.hanbai_tanka
FROM TenpoShohin AS TS LEFT OUTER JOIN Shohin AS s
	ON TS.shohin_id = S.shohin_id

In fact, there is no difference in their functions. You can use them Usually LEFT is used more often

Connect more than 3 tables

Join three tables

SELECT TS.tenpo_id, TS.tenpo_mei,TS.shohin_id,S.shohin_mei,S.hanbai_tanka,ZS.zaiko_suryo
FROM TenpoShohin AS TS INNER JOIN Shohin AS s
	ON TS.shohin_id = S.shohin_id
	INNER JOIN ZaikoShohin AS ZS
	ON TS.shohin_id = ZS.shohin_id