PostgreSQL - DENGAN Klausul

Di PostgreSQL, kueri WITH menyediakan cara untuk menulis pernyataan tambahan untuk digunakan dalam kueri yang lebih besar. Ini membantu dalam memecah kueri yang rumit dan besar menjadi bentuk yang lebih sederhana, yang mudah dibaca. Pernyataan ini sering disebut sebagai Common Table Expressions atau CTE, dapat dianggap sebagai tabel sementara yang ada hanya untuk satu kueri.

Kueri WITH menjadi kueri CTE, sangat berguna ketika subkueri dijalankan beberapa kali. Ini sama membantu di tempat tabel sementara. Ini menghitung agregasi satu kali dan memungkinkan kita untuk mereferensikannya dengan namanya (mungkin beberapa kali) dalam kueri.

Klausa WITH harus didefinisikan sebelum digunakan dalam query.

Sintaksis

Sintaks dasar dari kueri WITH adalah sebagai berikut -

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

Di mana name_for_summary_data adalah nama yang diberikan ke klausa WITH. Nama_for_summary_data bisa sama dengan nama tabel yang sudah ada dan akan diutamakan.

Anda dapat menggunakan pernyataan pengubah data (INSERT, UPDATE atau DELETE) di WITH. Ini memungkinkan Anda melakukan beberapa operasi berbeda dalam kueri yang sama.

Rekursif DENGAN

Kueri dengan atau hierarki rekursif, adalah bentuk CTE di mana CTE dapat merujuk ke dirinya sendiri, yaitu, kueri WITH dapat merujuk ke keluarannya sendiri, oleh karena itu disebut rekursif.

Contoh

Pertimbangkan tabel PERUSAHAAN memiliki catatan sebagai berikut -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Sekarang, mari kita tulis query menggunakan klausa WITH untuk memilih record dari tabel di atas, sebagai berikut -

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

Pernyataan PostgreSQL yang diberikan di atas akan menghasilkan hasil sebagai berikut -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Sekarang, mari kita tulis query menggunakan kata kunci RECURSIVE bersama dengan klausa WITH, untuk menemukan jumlah gaji kurang dari 20000, sebagai berikut -

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

Pernyataan PostgreSQL yang diberikan di atas akan menghasilkan hasil sebagai berikut -

sum
-------
 25000
(1 row)

Mari kita tulis kueri menggunakan pernyataan pengubah data bersama dengan klausa WITH, seperti yang ditunjukkan di bawah ini.

Pertama, buat tabel COMPANY1 yang mirip dengan tabel COMPANY. Kueri dalam contoh secara efektif memindahkan baris dari PERUSAHAAN ke PERUSAHAAN1. DELETE di WITH menghapus baris tertentu dari PERUSAHAAN, mengembalikan isinya melalui klausa RETURNING; dan kemudian kueri utama membaca keluaran itu dan memasukkannya ke dalam TABEL PERUSAHAAN -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

Pernyataan PostgreSQL yang diberikan di atas akan menghasilkan hasil sebagai berikut -

INSERT 0 3

Sekarang, catatan dalam tabel COMPANY dan COMPANY1 adalah sebagai berikut -

testdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)