MySQL学习笔记（一） 各种连接表的方法

MySQL 各种连接表的方法

语法

显式和隐式

1. 显式:

USE database;
SELECT *
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.id;

2. 隐式: (坚决不用)

USE database;
SELECT *
FROM table_1 t1, table_2 t2
WHERE t1.id = t2.id;

复合主键

USE database;
SELECT *
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.id AND t1.product_id = t2.product_id;

USING关键词

当所选关键列标签名相同，可以使用USING来简化语法。

USE database;
SELECT *
FROM table_1 t1
JOIN table_2 t2 USING (id);

UNION关键词

可以使得两个子查询合并结果。

USE sql_store;
SELECT 
	order_id,
	order_date,
	'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
	order_date,
	'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01';

INNER JOIN (内连接)

同一数据库内不同表连接

USE database;
SELECT *
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.id;

同一数据库内表自连接

USE sql_hr;
SELECT *
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id;

不同数据库不同表连接

USE database_1;
SELECT *
FROM table_1 t1
JOIN database_2.table_2 t2 ON t1.id = t2.id;

多表连接

USE database;
SELECT *
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.id
JOIN table_3 t3 ON t2.id = t3.id;

OUTER JOIN (外连接)

LEFT JOIN

左连接的表不论是否满足条件，都会返回结果，即保证左边一定全部输出，右边若无则输出NULL。

USE database;
SELECT *
FROM table_1 t1
LEFT JOIN table_2 t2 ON t1.id = t2.id;

RIGHT JOIN

右连接，同理，但是坚决不用，本质上和内连接实现的功能一样。

USE database;
SELECT *
FROM table_1 t1
RIGHT JOIN table_2 t2 ON t1.id = t2.id;

外连接表的自连接

USE sql_hr;
SELECT *
FROM employees e
LEFT JOIN employees m ON e.reports_to = m.employee_id;

CROSS JOIN

实现第一个表与第二个表的排列组合，通常会进行排序。

USE database;
SELECT *
FROM table_1 t1
CROSS JOIN table_2 t2 ON t1.id = t2.id
ORDER BY t1.name;

NATURAL JOIN

简短精炼，但坚决不用。

USE database;
SELECT *
FROM table_1 t1
NATURAL JOIN table_2 t2;