有用的BigQuery * EXCEPT 语法

使用和制作jOOQ最酷的事情之一是，我们可以发现供应商对标准SQL语言的最佳扩展，并通过模拟在jOOQ中增加对这些条款的支持。

这些语法之一是BigQuery的* EXCEPT 语法。每个曾经写过临时SQL查询的人都会希望这种语法可以使用，偶尔也可以。为什么需要它呢？看看这个查询：

SELECT * FROM actor

其结果是：

|actor_id|first_name|last_name   |last_update            |
|--------|----------|------------|-----------------------|
|1       |PENELOPE  |GUINESS     |2006-02-15 04:34:33.000|
|2       |NICK      |WAHLBERG    |2006-02-15 04:34:33.000|
|3       |ED        |CHASE       |2006-02-15 04:34:33.000|
|4       |JENNIFER  |DAVIS       |2006-02-15 04:34:33.000|
|5       |JOHNNY    |LOLLOBRIGIDA|2006-02-15 04:34:33.000|
|6       |BETTE     |NICHOLSON   |2006-02-15 04:34:33.000|
|...

但是那个LAST_UPDATE 列是非常烦人的，特别是当我们想NATURAL JOIN 的时候。例如，这样就不行了：

SELECT actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor AS a
NATURAL LEFT JOIN film_actor AS fa
GROUP BY actor_id

结果只是没有电影的演员，因为不小心，LAST_UPDATE 列被包括在NATURAL JOIN ：

|actor_id|first_name|last_name|count|
|--------|----------|---------|-----|
|58      |CHRISTIAN |AKROYD   |0    |
|8       |MATTHEW   |JOHANSSON|0    |
|116     |DAN       |STREEP   |0    |
|184     |HUMPHREY  |GARLAND  |0    |
|87      |SPENCER   |PECK     |0    |

这是NATURAL JOIN 的最大缺陷，使得它对于那些不是为NATURAL JOIN 的使用而完美设计的模式几乎毫无用处，但这是临时的SQL，如果能做到这一点就好了。

我们可以，如果我们有这样的* EXCEPT ：

SELECT 
  a.actor_id, 
  a.first_name, 
  a.last_name, 
  count(fa.film_id)
FROM (
  SELECT * EXCEPT (last_update) FROM actor
) AS a
NATURAL LEFT JOIN (
  SELECT * EXCEPT (last_update) FROM film_actor
) AS fa
GROUP BY 
  a.actor_id, 
  a.first_name, 
  a.last_name

不幸的是，这在PostgreSQL和其他方言中不起作用，但jOOQ可以模仿它。如果你向在线SQL翻译器提供了sakila数据库的表定义：

CREATE TABLE actor (
    actor_id integer NOT NULL,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);
CREATE TABLE film_actor (
    actor_id smallint NOT NULL,
    film_id smallint NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);

那么，它可以解决查询的EXCEPT 语法，并产生这样的结果：

SELECT
  a.actor_id,
  a.first_name,
  a.last_name,
  count(fa.film_id)
FROM (
  SELECT actor.actor_id, actor.first_name, actor.last_name
  FROM actor
) a
  NATURAL LEFT OUTER JOIN (
    SELECT film_actor.actor_id, film_actor.film_id
    FROM film_actor
  ) fa
GROUP BY
  a.actor_id,
  a.first_name,
  a.last_name

显然，我们可以省略NATURAL JOIN ，以达到相同的结果，但有时，在工具链中有另一个工具来编写一个漂亮的查询，这实在是太好了。用jOOQ，查询结果是这样的：

Actor a = ACTOR.as("a");
FilmActor fa = FILM_ACTOR.as("fa");

ctx.select(
        a.ACTOR_ID,
        a.FIRST_NAME,
        a.LAST_NAME,
        count(fa.FILM_ID))
   .from(
        select(asterisk().except(a.LAST_UPDATE)).from(a).asTable(a))
   .naturalLeftOuterJoin(
        select(asterisk().except(fa.LAST_UPDATE)).from(fa).asTable(fa))
   .groupBy(a.ACTOR_ID, a.FIRST_NAME, a.LAST_NAME)
   .fetch();