FROM a, b, c, d, e, f, g, h
WHERE a.a1 = b.bx
AND a.a2 = c.c1
AND d.d1 = b.bc
-- etc...
使用join来查询表的语法
更安全,你可以把连接谓词与连接表放一起,从而防止错误。
更富于表现力,你可以区分外部连接,内部连接,等等。
从中我们学到了什么呢?
使用JOIN,并且永远不在FROM语句中使用逗号分隔表引用。
6、SQL的不同类型的连接操作
连接操作基本上有五种
EQUI JOIN
SEMI JOIN
ANTI JOIN
CROSS JOIN
DIVISION
这些术语通常用于关系代数。对上述概念,如果他们存在,SQL会使用不同的术语。让我们仔细看看:
EQUI JOIN(同等连接)
这是最常见的JOIN操作。它有两个子操作:
INNER JOIN(或者只是JOIN)
OUTER JOIN(可以再次拆分为LEFT, RIGHT,FULL OUTER JOIN)
例子是其中的区别最好的解释:
-- This table reference contains authors and their books.
-- There is one record for each book and its author.
-- authors without books are NOT included
author JOIN book ON author.id = book.author_id
-- This table reference contains authors and their books
-- There is one record for each book and its author.
-- ... OR there is an "empty" record for authors without books
-- ("empty" meaning that all book columns are NULL)
author LEFT OUTER JOIN book ON author.id = book.author_id
-- Using IN
FROM author
WHERE author.id IN (SELECT book.author_id FROM book)
-- Using EXISTS
FROM author
WHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
这个关系的概念跟半连接刚好相反。您可以简单地通过将 NOT 关键字添加到IN 或 EXISTS中生成它。在下例中,我们选择那些没有任何书籍的作者:
-- Using IN
FROM author
WHERE author.id NOT IN (SELECT book.author_id FROM book)
-- Using EXISTS
FROM author
WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)
-- Get authors' first and last names, and their age in days
SELECT first_name, last_name, age
FROM (
SELECT first_name, last_name, current_date - date_of_birth age
FROM author
)
-- If the age is greater than 10000 days
WHERE age > 10000
网友评论
文明上网理性发言,请遵守 新闻评论服务协议
我要评论