SELECT * FROM <表1> <表2>
SELECT * FROM students, classes;
查询的结果是一个二维表,它是students表和classes表的“乘积”
,即students表的每一行与classes表的每一行都两两拼在一起返回
结果集的列数是两表的列数之和,行数是两表行数之积(要小心,乘积有可能很大)。
SELECT
students.id sid,
students.name,
students.gender,
students.score,
classes.id cid,
classes.name cname
FROM students, classes;
SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c;
WHERE
条件SELECT
s.id sid,
s.name,
s.gender,
s.score,
c.id cid,
c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;
1班的男生
连接查询是另一种类型的多表查询。
连接查询对多个表进行JOIN
运算:
有选择性
“连接”在主表结果集上选出所有学生的信息
SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;
我们还需要班级的 名称
INNER JOIN
来实现SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s 主表
INNER JOIN classes c 需要连接的表
ON s.class_id = c.id; ON 条件
可选:加上WHERE子句、ORDER BY等子句
LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN’
SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;
题目:
Create table Person (PersonId int, FirstName varchar(255), LastName varchar(255))
Create table Address (AddressId int, PersonId int, City varchar(255), State varchar(255))
Truncate table Person
insert into Person (PersonId, LastName, FirstName) values ('1', 'Wang', 'Allen')
Truncate table Address
insert into Address (AddressId, PersonId, City, State) values ('1', '2', 'New York City', 'New York')
表1: Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是上表主键
表2: Address
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,
都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
来源:力扣(LeetCode) 链接:https://leetcode-cn.com/problems/combine-two-tables 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
解题:
# Write your MySQL query statement below
SELECT
P.FirstName, P.LastName, A.City, A.State
FROM Person P
LEFT OUTER JOIN Address A
ON P.PersonId = A.PersonId
369 ms
题目:
Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
来源:力扣(LeetCode) 链接:https://leetcode-cn.com/problems/employees-earning-more-than-their-managers 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
解题:
把同一份表再次JOIN
该表,条件是A.ManagerId = B.Id
# Write your MySQL query statement below
SELECT A.Name Employee
FROM Employee A
LEFT OUTER JOIN Employee B
ON A.ManagerId = B.Id
WHERE A.Salary > B.Salary
或者
# Write your MySQL query statement below
SELECT A.Name Employee
FROM Employee A
INNER JOIN Employee B
ON A.ManagerId = B.Id
WHERE A.Salary > B.Salary
309 ms