SQL语句_链表(上)

说到连表查询，我们先了解下别名。别名可以用在表上，也可以用在表中参数名。即 SELECT "表格别名". "表中参数名" "表中参数别名" FROM “表格名”  "表格别名"  或 SELECT "表格别名". "表中参数名" AS "表中参数别名" FROM “表格名” AS "表格别名"  举个例子  SELECT SI.store_name STORE, SUM(SI.sales) Total Sales FROM Story_Info SI GROUP BY SI.store_name HAVING SUM(SI.sales) > 1000; 

STORE	Total Sales
A	2000

---

 

Store_Info表：

store_name	sales	Date
A	500	01-01-2000
B	200	02-01-2000
C	1500	02-10-2000
D	1000	03-08-2000

Factory表：

factory_name	store_name
FA	A
FB	B
FC	C
FE	E

链表查询，SQL语句中最基本的就是JOIN，可分为

INNER JOIN  两个表中，只匹配相同的列值所在行的数据。

SELECT * From Store_Info A INNER JOIN Factory B ON A.store_name = B.store_name 得出的结果：

store_name	sales	Date	factory_name	store_name
A	500	01-01-2000	FA	A
B	200	02-01-2000	FB	B
C	1500	02-10-2000	FC	C

LEFT OUTER JOIN  返回左表的所有数据，右表中不能匹配的列值，其所在行使用空值

SELECT * FROM Store_Info A LEFT OUTER JOIN Factory B ON A.store_name = B.store_name 得出的结果：

store_name	sales	Date	factory_name	store_name
A	500	01-01-2000	FA	A
B	200	02-01-2000	FB	B
C	1500	02-10-2000	FC	C
D	1000	03-08-2000

 

LEFT OUTER JOIN - WHERE NULL  返回和右表不匹配的所有数据行

SELECT * FROM Store_Info A LEFT OUTER JOIN Factory B ON A.store_name = B.store_name WHERE B.store_name IS NULL  得出的结果：

store_name	sales	Date	factory_name	store_name
D	1000	03-08-2000

 

RIGHT OUTER JOIN  返回右表的所有数据，左表中不能匹配的列值，其所在行使用空值

SELECT * FROM Store_Info A RIGHT OUTER JOIN Factory B ON A.store_name = B.store_name 得出的结果：

store_name	sales	Date	factory_name	store_name
A	500	01-01-2000	FA	A
B	200	02-01-2000	FB	B
C	1500	02-10-2000	FC	C
			FE	E

 

RIGHT OUTER JOIN - WHERE NULL  返回和左表不匹配的所有数据行

SELECT * FROM Store_Info A RIGHT OUTER JOIN Factory B ON A.store_name = B.store_name WHERE A.store_name IS NULL  得出的结果：

store_name	sales	Date	factory_name	store_name
			FE	E

 

FULL OUTER JOIN  返回两个表的所有数据，如果匹配的列的值两个表都有，返回数据行，否则返回空值。

SELECT * From Store_Info A FULL OUTER JOIN Factory B ON A.store_name = B.store_name  得出的结果： 

store_name	sales	Date	factory_name	store_name
A	500	01-01-2000	FA	A
B	200	02-01-2000	FB	B
C	1500	02-10-2000	FC	C
D	1000	03-08-2000
			FE	E

 

FULL OUTER JOIN -WHERE NULL  返回 INNER JOIN以外的数据行

SELECT * FROM Store_Info A FULL OUTER JOIN Factory B ON A.store_name = B.store_name WHERE A.store_name IS NULL OR B.store_name IS NULL  得出的结果：

store_name	sales	Date	factory_name	store_name
D	1000	03-08-2000
			FE	E