The Helpful BigQuery * EXCEPT Syntax – Java, SQL and jOOQ.

One of many coolest issues about utilizing and making jOOQ is that we get to find the very best extensions to the usual SQL language by distributors, and add assist for these clauses in jOOQ through emulations.

One in all these syntaxes is BigQuery’s * EXCEPT syntax. Everybody who ever wrote ad-hoc SQL queries would have appreciated this syntax to be accessible, sometimes. Why is it wanted? Take a look at this question:

The consequence being:

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

However that LAST_UPDATE column is mighty annoying, particularly if we need to NATURAL JOIN issues. E.g. this doesn’t work:

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

The result’s simply actors with out movies, as a result of unintentionally, the LAST_UPDATE column was included within the NATURAL JOIN:

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

That is the most important flaw of NATURAL JOIN, making it virtually ineffective for schemas that aren’t completely designed for NATURAL JOIN utilization, however that is ad-hoc SQL, and it might have been good to try this.

We may, if we had * EXCEPT like this:

SELECT 
  a.actor_id, 
  a.first_name, 
  a.last_name, 
  depend(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

Sadly, this doesn’t work in PostgreSQL and different dialects, however jOOQ can emulate it. In case you present the on-line SQL translator with the sakila database desk definitions:

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

Then, it could actually resolve the EXCEPT syntax of the question and produce this:

SELECT
  a.actor_id,
  a.first_name,
  a.last_name,
  depend(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

Clearly, we may have simply omitted NATURAL JOIN to attain the identical consequence, however typically, it’s simply good to have yet one more device within the device chain to jot down a pleasant question. With jOOQ, the question would learn:

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

ctx.choose(
        a.ACTOR_ID,
        a.FIRST_NAME,
        a.LAST_NAME,
        depend(fa.FILM_ID))
   .from(
        choose(asterisk().besides(a.LAST_UPDATE)).from(a).asTable(a))
   .naturalLeftOuterJoin(
        choose(asterisk().besides(fa.LAST_UPDATE)).from(fa).asTable(fa))
   .groupBy(a.ACTOR_ID, a.FIRST_NAME, a.LAST_NAME)
   .fetch();