细碎的sql 语句

JOIN 技巧

• 记着要尽量使用 JOIN 进行表的连接，永远不要在 FROM 后面使用逗号连接表
  利用逗号来简化 SQL 语句有时候会造成思维上的混乱，想一下下面的语句：

FROM a, b, c, d, e, f, g, h
WHERE a.a1 = b.bx
AND a.a2 = c.c1
AND d.d1 = b.bc

IN 和 EXISTS

1. 下面的语句 IN，EXISTS 比 JOIN 高效

-- 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)

-- Find only those authors who also have books
SELECT DISTINCT first_name, last_name
FROM author
JOIN book ON author.id = book.author_id

1. EXISTS 只返回TRUE 或 FALSE，当IN遇到包含NULL的情况时会返回UNKOWN。