HSQLDB - Associações

Sempre que houver um requisito para recuperar dados de várias tabelas usando uma única consulta, você pode usar JOINS do RDBMS. Você pode usar várias tabelas em sua única consulta SQL. O ato de ingressar em HSQLDB refere-se a quebrar duas ou mais tabelas em uma única tabela.

Considere as seguintes tabelas Clientes e Pedidos.

Customer:
+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  |  SALARY  |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad |  2000.00 |
| 2  |  Khilan  | 25  |   Delhi   |  1500.00 |
| 3  |  kaushik | 23  |   Kota    |  2000.00 |
| 4  | Chaitali | 25  |   Mumbai  |  6500.00 |
| 5  |  Hardik  | 27  |   Bhopal  |  8500.00 |
| 6  |  Komal   | 22  |    MP     |  4500.00 |
| 7  |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+
Orders:
+-----+---------------------+-------------+--------+
|OID  |         DATE        | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |      3      |  3000  |
| 100 | 2009-10-08 00:00:00 |      3      |  1500  |
| 101 | 2009-11-20 00:00:00 |      2      |  1560  |
| 103 | 2008-05-20 00:00:00 |      4      |  2060  |
+-----+---------------------+-------------+--------+

Agora, vamos tentar recuperar os dados dos clientes e o valor do pedido que o respetivo cliente efetuou. Isso significa que estamos recuperando os dados de registro da tabela de clientes e pedidos. Podemos conseguir isso usando o conceito JOINS no HSQLDB. A seguir está a consulta JOIN para o mesmo.

SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.ID =
ORDERS.CUSTOMER_ID;

Após a execução da consulta acima, você receberá a seguinte saída.

+----+----------+-----+--------+
| ID |   NAME   | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |  3000  |
|  3 | kaushik  |  23 |  1500  |
|  2 | Khilan   |  25 |  1560  |
|  4 | Chaitali |  25 |  2060  |
+----+----------+-----+--------+

Tipos de JOIN

Existem diferentes tipos de junções disponíveis no HSQLDB.

INNER JOIN - Retorna as linhas quando há uma correspondência em ambas as tabelas.
LEFT JOIN - Retorna todas as linhas da tabela da esquerda, mesmo se não houver correspondências na tabela da direita.
RIGHT JOIN - Retorna todas as linhas da tabela da direita, mesmo se não houver correspondências na tabela da esquerda.
FULL JOIN - Retorna as linhas quando há uma correspondência em uma das tabelas.
SELF JOIN - Usado para juntar uma tabela a si mesma como se a tabela fosse duas tabelas, renomeando temporariamente pelo menos uma tabela na instrução SQL.

Junção interna

A junção mais freqüentemente usada e importante é a INNER JOIN. Ele também é conhecido como EQUIJOIN.

O INNER JOIN cria uma nova tabela de resultados combinando os valores das colunas de duas tabelas (tabela1 e tabela2) com base no predicado de junção. A consulta compara cada linha da tabela1 com cada linha da tabela2 para encontrar todos os pares de linhas que satisfazem o predicado de junção. Quando o predicado de junção é satisfeito, os valores da coluna para cada par correspondente de linhas A e B são combinados em uma linha de resultado.

Sintaxe

A sintaxe básica de INNER JOIN é a seguinte.

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Exemplo

Considere as duas tabelas a seguir, uma intitulada como tabela CLIENTES e outra intitulada tabela PEDIDOS, da seguinte forma -

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  | SALARY   |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad | 2000.00  |
| 2  |  Khilan  | 25  |   Delhi   | 1500.00  |
| 3  |  kaushik | 23  |   Kota    | 2000.00  |
| 4  | Chaitali | 25  |   Mumbai  | 6500.00  |
| 5  |  Hardik  | 27  |   Bhopal  | 8500.00  |
| 6  |  Komal   | 22  |     MP    | 4500.00  |
| 7  |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |         DATE        | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |      3      | 3000   |
| 100 | 2009-10-08 00:00:00 |      3      | 1500   |
| 101 | 2009-11-20 00:00:00 |      2      | 1560   |
| 103 | 2008-05-20 00:00:00 |      4      | 2060   |
+-----+---------------------+-------------+--------+

Agora, vamos juntar essas duas tabelas usando a consulta INNER JOIN da seguinte maneira -

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Após a execução da consulta acima, você receberá a seguinte saída.

+----+----------+--------+---------------------+
| ID |   NAME   | AMOUNT |         DATE        |
+----+----------+--------+---------------------+
| 3  |  kaushik |  3000  | 2009-10-08 00:00:00 |
| 3  |  kaushik |  1500  | 2009-10-08 00:00:00 |
| 2  |  Khilan  |  1560  | 2009-11-20 00:00:00 |
| 4  | Chaitali |  2060  | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

Associação à esquerda

O HSQLDB LEFT JOIN retorna todas as linhas da tabela à esquerda, mesmo se não houver correspondências na tabela à direita. Isso significa que se a cláusula ON corresponder a 0 (zero) registros na tabela certa, a junção ainda retornará uma linha no resultado, mas com NULL em cada coluna da tabela certa.

Isso significa que uma junção à esquerda retorna todos os valores da tabela à esquerda, mais os valores correspondentes da tabela à direita ou NULL no caso de nenhum predicado de junção correspondente.

Sintaxe

A sintaxe básica de LEFT JOIN é a seguinte -

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Aqui, a condição fornecida pode ser qualquer expressão com base em seus requisitos.

Exemplo

Considere as duas tabelas a seguir, uma intitulada como tabela CLIENTES e outra intitulada tabela PEDIDOS, da seguinte forma -

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  | SALARY   |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad | 2000.00  |
| 2  |  Khilan  | 25  |   Delhi   | 1500.00  |
| 3  |  kaushik | 23  |   Kota    | 2000.00  |
| 4  | Chaitali | 25  |   Mumbai  | 6500.00  |
| 5  |  Hardik  | 27  |   Bhopal  | 8500.00  |
| 6  |  Komal   | 22  |    MP     | 4500.00  |
| 7  |  Muffy   | 24  |  Indore   | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |        DATE         | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |     3       | 3000   |
| 100 | 2009-10-08 00:00:00 |     3       | 1500   |
| 101 | 2009-11-20 00:00:00 |     2       | 1560   |
| 103 | 2008-05-20 00:00:00 |     4       | 2060   |
+-----+---------------------+-------------+--------+

Agora, vamos juntar essas duas tabelas usando a consulta LEFT JOIN da seguinte maneira -

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Após a execução da consulta acima, você receberá a seguinte saída -

+----+----------+--------+---------------------+
| ID |   NAME   | AMOUNT |         DATE        |
+----+----------+--------+---------------------+
|  1 |  Ramesh  |  NULL  |        NULL         |
|  2 |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|  3 |  kaushik |  3000  | 2009-10-08 00:00:00 |
|  3 |  kaushik |  1500  | 2009-10-08 00:00:00 |
|  4 | Chaitali |  2060  | 2008-05-20 00:00:00 |
|  5 |  Hardik  |  NULL  |        NULL         |
|  6 |  Komal   |  NULL  |        NULL         |
|  7 |  Muffy   |  NULL  |        NULL         |
+----+----------+--------+---------------------+

Junção certa

O HSQLDB RIGHT JOIN retorna todas as linhas da tabela da direita, mesmo se não houver correspondências na tabela da esquerda. Isso significa que se a cláusula ON corresponder a 0 (zero) registros na tabela à esquerda, a junção ainda retornará uma linha no resultado, mas com NULL em cada coluna da tabela à esquerda.

Isso significa que uma junção à direita retorna todos os valores da tabela à direita, mais os valores correspondentes da tabela à esquerda ou NULL no caso de nenhum predicado de junção correspondente.

Sintaxe

A sintaxe básica de RIGHT JOIN é o seguinte -

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Exemplo

Considere as duas tabelas a seguir, uma intitulada como tabela CLIENTES e outra intitulada tabela PEDIDOS, da seguinte forma -

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  |  SALARY  |
+----+----------+-----+-----------+----------+
| 1  |  Ramesh  | 32  | Ahmedabad |  2000.00 |
| 2  |  Khilan  | 25  |   Delhi   |  1500.00 |
| 3  |  kaushik | 23  |   Kota    |  2000.00 |
| 4  | Chaitali | 25  |   Mumbai  |  6500.00 |
| 5  |  Hardik  | 27  |   Bhopal  |  8500.00 |
| 6  |  Komal   | 22  |     MP    |  4500.00 |
| 7  |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |       DATE          | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |      3      |  3000  |
| 100 | 2009-10-08 00:00:00 |      3      |  1500  |
| 101 | 2009-11-20 00:00:00 |      2      |  1560  |
| 103 | 2008-05-20 00:00:00 |      4      |  2060  |
+-----+---------------------+-------------+--------+

Agora, vamos juntar essas duas tabelas usando a consulta RIGHT JOIN da seguinte maneira -

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Após a execução da consulta acima, você receberá o seguinte resultado.

+------+----------+--------+---------------------+
|  ID  |   NAME   | AMOUNT |        DATE         |
+------+----------+--------+---------------------+
|  3   |  kaushik |  3000  | 2009-10-08 00:00:00 |
|  3   |  kaushik |  1500  | 2009-10-08 00:00:00 |
|  2   |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|  4   | Chaitali |  2060  | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

Full Join

O HSQLDB FULL JOIN combina os resultados das junções externas esquerda e direita.

A tabela associada conterá todos os registros de ambas as tabelas e preencherá NULLs para as correspondências ausentes em ambos os lados.

Sintaxe

A sintaxe básica de FULL JOIN é a seguinte -

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

Aqui, a condição fornecida pode ser qualquer expressão com base em seus requisitos.

Exemplo

Considere as duas tabelas a seguir, uma intitulada como tabela CLIENTES e outra intitulada tabela PEDIDOS, da seguinte forma -

+----+----------+-----+-----------+----------+
| ID |   NAME   | AGE |  ADDRESS  |  SALARY  |
+----+----------+-----+-----------+----------+
|  1 |  Ramesh  | 32  | Ahmedabad | 2000.00  |
|  2 |  Khilan  | 25  |   Delhi   | 1500.00  |
|  3 |  kaushik | 23  |   Kota    | 2000.00  |
|  4 | Chaitali | 25  |   Mumbai  | 6500.00  |
|  5 |  Hardik  | 27  |   Bhopal  | 8500.00  |
|  6 |  Komal   | 22  |   MP      | 4500.00  |
|  7 |  Muffy   | 24  |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

+-----+---------------------+-------------+--------+
| OID |         DATE        | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |    3        | 3000   |
| 100 | 2009-10-08 00:00:00 |    3        | 1500   |
| 101 | 2009-11-20 00:00:00 |    2        | 1560   |
| 103 | 2008-05-20 00:00:00 |    4        | 2060   |
+-----+---------------------+-------------+--------+

Agora, vamos juntar essas duas tabelas usando a consulta FULL JOIN da seguinte maneira -

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Após a execução da consulta acima, você receberá o seguinte resultado.

+------+----------+--------+---------------------+
|  ID  |    NAME  | AMOUNT |        DATE         |
+------+----------+--------+---------------------+
|   1  |  Ramesh  |  NULL  |        NULL         |
|   2  |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|   3  |  kaushik |  3000  | 2009-10-08 00:00:00 |
|   3  |  kaushik |  1500  | 2009-10-08 00:00:00 |
|   4  | Chaitali |  2060  | 2008-05-20 00:00:00 |
|   5  |  Hardik  |  NULL  |        NULL         |
|   6  |   Komal  |  NULL  |        NULL         |
|   7  |   Muffy  |  NULL  |        NULL         |
|   3  |  kaushik |  3000  | 2009-10-08 00:00:00 |
|   3  |  kaushik |  1500  | 2009-10-08 00:00:00 |
|   2  |  Khilan  |  1560  | 2009-11-20 00:00:00 |
|   4  | Chaitali |  2060  | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

Self Join

O SQL SELF JOIN é usado para juntar uma tabela a si mesma como se a tabela fosse duas tabelas, renomeando temporariamente pelo menos uma tabela na instrução SQL.

Sintaxe

A sintaxe básica de SELF JOIN é a seguinte -

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

Aqui, a cláusula WHERE pode ser qualquer expressão com base em seus requisitos.

Exemplo

Considere as duas tabelas a seguir, uma intitulada como tabela CLIENTES e outra intitulada tabela PEDIDOS, da seguinte forma -

+----+----------+-----+-----------+----------+
| ID |    NAME  | AGE |   ADDRESS |   SALARY |
+----+----------+-----+-----------+----------+
|  1 |  Ramesh  |  32 | Ahmedabad | 2000.00  |
|  2 |  Khilan  |  25 |   Delhi   | 1500.00  |
|  3 |  kaushik |  23 |   Kota    | 2000.00  |
|  4 | Chaitali |  25 |   Mumbai  | 6500.00  |
|  5 |  Hardik  |  27 |   Bhopal  | 8500.00  |
|  6 |  Komal   |  22 |   MP      | 4500.00  |
|  7 |  Muffy   |  24 |   Indore  | 10000.00 |
+----+----------+-----+-----------+----------+

Agora, vamos juntar esta tabela usando a consulta SELF JOIN da seguinte maneira -

SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY > b.SALARY;

Após a execução da consulta acima, você receberá a seguinte saída -

+----+----------+---------+
| ID |   NAME   | SALARY  |
+----+----------+---------+
| 2  |  Ramesh  | 1500.00 |
| 2  |  kaushik | 1500.00 |
| 1  | Chaitali | 2000.00 |
| 2  | Chaitali | 1500.00 |
| 3  | Chaitali | 2000.00 |
| 6  | Chaitali | 4500.00 |
| 1  |  Hardik  | 2000.00 |
| 2  |  Hardik  | 1500.00 |
| 3  |  Hardik  | 2000.00 |
| 4  |  Hardik  | 6500.00 |
| 6  |  Hardik  | 4500.00 |
| 1  |  Komal   | 2000.00 |
| 2  |  Komal   | 1500.00 |
| 3  |  Komal   | 2000.00 |
| 1  |  Muffy   | 2000.00 |
| 2  |  Muffy   | 1500.00 |
| 3  |  Muffy   | 2000.00 |
| 4  |  Muffy   | 6500.00 |
| 5  |  Muffy   | 8500.00 |
| 6  |  Muffy   | 4500.00 |
+----+----------+---------+