几个复杂查询语句相关内容

一、查询SQL格式：
SELECT <>
FROM <>
[LEFT、RIGHT] JOIN <>
WHERE <>
GROUP BY <>
HAVINT <>
ORDER BY<>
LIMIT <>,<>

二、“A 且 排除B”方式查询：

A 且 非B

1、使用连接查询
SELECT <> FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL
巧妙利用连接查询特点，将a表的在b表中无对应项的记录查出
2、使用EXISTS关键字查询
SELECT <> FROM a WHERE NOT EXISTS ( SELECT * FROM b WHERE a.id = b.id )

三、MySQL中代替“FULL JOIN”的用法：
MySQL中无“FULL JOIN”的用法，可使用UNION关键字拼接左右连接查询语句并对查询结果去重，最终实现需要的查询

A 且 B

SELECT <> FROM a LEFT JOIN b ON a.id = b.id
UNION
SELECT <> FROM a RIGHT JOIN b ON a.id = b.id

A且B且非AB

SELECT <> FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL
UNION
SELECT <> FROM a RIGHT JOIN b ON a.id = b.id WHERE a.id IS NULL