595.大的国家 OR或UNION
满足两个条件之一即可,可以用OR将两个子查询合并在一起
SELECT
name,population,area
FROM
World
WHERE
area>=3000000 OR population>=25000000;
通过UNION连接子查询
UNION:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序,即:去重+排序
UNION All:对两个结果集进行并集操作,包括重复行,不进行排序,即:不去重+不排序
SELECT
name,population,area
FROM
World
WHERE
area>=3000000
UNION
SELECT
name,population,area
FROM
World
WHERE
population>=25000000;
1757.可回收且低脂的产品 AND
SELECT
product_id
FROM
Products
WHERE
low_fats='Y' AND recyclable='Y';
584.寻找用户推荐人 NULL <>(!=)
推荐人的编号不是2,即为NULL或者除2外的其他数字
SELECT
name
FROM
customer
WHERE
referee_id IS NULL OR referee_id != 2;
183.从不订购的客户
左连接,没有订购的客户的Orders.Id是NULL
SELECT
c.Name AS Customers
FROM
Customers c
LEFT JOIN
Orders o
ON
c.Id = o.CustomerId
WHERE
o.Id IS NULL;
嵌套,从Orders中获取订购商品的客户Id,从Customers中选取不在这部分Id中的客户名称
SELECT
c.Name AS Customers
From
Customers c
WHERE
c.Id NOT IN
(SELECT CustomerId FROM Orders);