Hai semuanya, di materi kali ini kita akan membahas tentang Menggabungkan beberapa query menjadi suatu resutlset atau klo bahas kerennya Combining Queries. Pada combining queries ini ada beberapa macam teknik atau metode diataranya

  1. UNION
  2. INTERSECT
  3. EXCEPT

Dari setiap method tersebut memiliki fungsinya masing-masing, Untuk lebih jelasnya yukk langsung aja kita bahas satu-per-satu

Using UNION queries

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

The syntax:

select ... from ... 
UNION [ distinct | ALL ]
select ... from ...

Berikut adalah contohnya

Jika dijalankan maka hasilnya seperti berikut:

hr=# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data1
hr-# union distinct
hr-# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (4, 'abdul', 'Abdul Rahman', false)
hr(#      ) as data2;
 column1 | column2  |     column3     | column4 
---------+----------+-----------------+---------
       4 | abdul    | Abdul Rahman    | f
       3 | mpurwadi | Muhamad Purwadi | f
       2 | myusuf   | Muhamad Yusuf   | t
       1 | dimasm93 | Dimas Maryanto  | t
(4 rows)

Nah jadi klo temen-temen perhatikan dari hasil diatas, hasilnya akan dibersihkan dari nilai yang redudansi (duplicate). Klausa UNION by default menggunakan DISTINCT, jadi akan memfilter nilai yang duplicate. Jika mau menampilkan semuanya kita bisa klausa UNION ALL seperti berikut:

Jika dijalankan hasilnya seperti berikut:

hr=# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data1
hr-# union all
hr-# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (4, 'abdul', 'Abdul Rahman', false)
hr(#      ) as data2;
 column1 | column2  |     column3     | column4 
---------+----------+-----------------+---------
       1 | dimasm93 | Dimas Maryanto  | t
       2 | myusuf   | Muhamad Yusuf   | t
       3 | mpurwadi | Muhamad Purwadi | f
       1 | dimasm93 | Dimas Maryanto  | t
       4 | abdul    | Abdul Rahman    | f
(5 rows)

Using INTERSECT queries

INTERSECT returns all rows that are both in the result of query1 and in the result of query2. Duplicate rows are eliminated unless INTERSECT ALL is used.

The syntax:

select ... from ... 
INTERSECT [ ALL ]
select ... from ...

Berikut adalah contohnya

Jika dijalankan hasilnya seperti berikut:

hr=# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data1
hr-# INTERSECT DISTINCT
hr-# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (4, 'abdul', 'Abdul Rahman', false),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data2;
 column1 | column2  |     column3     | column4 
---------+----------+-----------------+---------
       3 | mpurwadi | Muhamad Purwadi | f
       1 | dimasm93 | Dimas Maryanto  | t
(2 rows)

Jika temen-temen perhatikan, INTERSECT hanya mengambil data yang sama saja pada kedua query tersebut, Sekarang kita coba menggunakan INTERSECT ALL dengan query seperti berikut:

Jika dijalankan hasilnya seperti berikut:

hr=# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data1
hr-# INTERSECT ALL
hr-# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (4, 'abdul', 'Abdul Rahman', false),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data2;
 column1 | column2  |     column3     | column4 
---------+----------+-----------------+---------
       3 | mpurwadi | Muhamad Purwadi | f
       2 | myusuf   | Muhamad Yusuf   | t
       2 | myusuf   | Muhamad Yusuf   | t
       1 | dimasm93 | Dimas Maryanto  | t
(4 rows)

Using EXCEPT queries

EXCEPT returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.) Again, duplicates are eliminated unless EXCEPT ALL is used.

The syntax:

select ... from ... 
EXCEPT [ ALL ]
select ... from ...

Berikut adalah contohnya

Jika dijalankan hasilnya seperti berikut:

hr=# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data1
hr-# EXCEPT
hr-# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (4, 'abdul', 'Abdul Rahman', false)
hr(#      ) as data2;
 column1 | column2  |     column3     | column4 
---------+----------+-----------------+---------
       3 | mpurwadi | Muhamad Purwadi | f
       2 | myusuf   | Muhamad Yusuf   | t
(2 rows)

Jika temen-temen perhatikan dari hasil query diatas, dimana hasilnya adalah nilai yang tidak terdapat pada query keduanya. Atau jika analogikan kita bisa menggunakan select ... from ... where id NOT IN (subquery). Sekarang kita coba menggunakan EXCEPT ALL berikut querynya:

Jika dijalankan hasilnya seperti berikut:

hr=# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data1
hr-# EXCEPT ALL
hr-# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (4, 'abdul', 'Abdul Rahman', false)
hr(#      ) as data2;
 column1 | column2  |     column3     | column4 
---------+----------+-----------------+---------
       3 | mpurwadi | Muhamad Purwadi | f
       3 | mpurwadi | Muhamad Purwadi | f
       2 | myusuf   | Muhamad Yusuf   | t
(3 rows)

Using Combination all of them

In order to calculate the union, intersection, or difference of two queries, the two queries must be “union compatible”, which means that they return the same number of columns and the corresponding columns have compatible data types

Set operations can be combined:

query1 UNION query2 EXCEPT query3

which is equivalent to

(query1 UNION query2) EXCEPT query3

As shown here, you can use parentheses to control the order of evaluation. Without parentheses, UNION and EXCEPT associate left-to-right, but INTERSECT binds more tightly than those two operators. Thus

query1 UNION query2 INTERSECT query3

means

query1 UNION (query2 INTERSECT query3)

For examples:

Jika dijalankan hasilnya seperti berikut:

hr=# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (2, 'myusuf', 'Muhamad Yusuf', true),
hr(#              (3, 'mpurwadi', 'Muhamad Purwadi', false)
hr(#      ) as data1
hr-# union
hr-# distinct
hr-# select *
hr-# from (values (1, 'dimasm93', 'Dimas Maryanto', true),
hr(#              (4, 'abdul', 'Abdul Rahman', false)
hr(#      ) as data2
hr-# except
hr-# select *
hr-# from (values (2, 'myusuf', 'Muhamad Yusuf', true)
hr(#      ) as data3;
 column1 | column2  |     column3     | column4 
---------+----------+-----------------+---------
       4 | abdul    | Abdul Rahman    | f
       3 | mpurwadi | Muhamad Purwadi | f
       1 | dimasm93 | Dimas Maryanto  | t
(3 rows)