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

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

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

[ad_1]

One of many coolest issues about utilizing and making jOOQ is that we get to find one of the best extensions to the usual SQL language by distributors, and add help 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? Have a look at this question:

The end result 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, rely(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|rely|
|--------|----------|---------|-----|
|58      |CHRISTIAN |AKROYD   |0    |
|8       |MATTHEW   |JOHANSSON|0    |
|116     |DAN       |STREEP   |0    |
|184     |HUMPHREY  |GARLAND  |0    |
|87      |SPENCER   |PECK     |0    |

That is the largest flaw of NATURAL JOIN, making it nearly ineffective for schemas that aren’t completely designed for NATURAL JOIN utilization, however that is ad-hoc SQL, and it could have been good to do this.

We might, if we had * EXCEPT like this:

SELECT 
  a.actor_id, 
  a.first_name, 
  a.last_name, 
  rely(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. When 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 will probably resolve the EXCEPT syntax of the question and produce this:

SELECT
  a.actor_id,
  a.first_name,
  a.last_name,
  rely(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 might have simply omitted NATURAL JOIN to realize the identical end result, however generally, it’s simply good to have yet one more instrument within the instrument chain to write 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,
        rely(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();

[ad_2]

Supply hyperlink

LEAVE A REPLY

Please enter your comment!
Please enter your name here