Queries inside a query (subquery)

PostgreSQL May 6, 2022 24 minute read

Hai semuanya, setelah kita membahas tentang Joined tables tahap selanjutnya kita akan membahas tentang Queries inside a query atau lebih di kenal dengan SubQuery. Seperti biasa karena pembahasannya akan lumayan panjang jadi kita akan bagi-bagi menjadi beberapa bagian ya diantaranya:

What is SubQuery?
Using SubQuery specified in column_list
Correlate SubQuery
Using SubQuery inline view
Lateral Subqueries
SubQuery as predicate in where clause

OK langsung aja kita ke pembahasan yang pertama

What is SubQuery?

A subquery is a query nested inside another query such as SELECT, INSERT, DELETE and UPDATE. In this article, we are focusing on the SELECT statement only.

Subquery in select statement can appear into select, from and where clause, The basic syntax of subquery look like:

select select_column_list, (single_row_subquery_expression)
from from_tables [, (multi_columns_subquery_expression)]
where column_name operator (single_column_subquery_expression)

Dimana single_row_subquery_expression adalah suatu query yang biasanya menghasilkan data 1 row dan 1 column contohnya:

select avg(salary)
from employees;

Kemudian untuk multiple_columns_subquery_expression adalah suatu query yang bisa menghasilkan banyak data dan banyak column contohnya:

select employee_id, first_name, last_name
from employees;

dan yang terakhir untuk single_column_subquery_expression adalah suatu query yang bisa menghasilkan banyak data dan hanya 1 column saja contohnya:

select salary
from employees;

PostgreSQL executes the query that contains a subquery in the following sequence:

executes the subquery.
gets the result and passes it to the outer query.
executes the outer query.

Untuk lebih detailnya seperti ilustrasi seperti berikut:

subquery-execution-process

Using SubQuery specified in `select_column_list`

SubQuery yang paling umum pada Select statement, di letakan pada column_list dan where clause. Kali ini kita bahas dulu untuk SubQuery pada column_list. The basic query form is

select select_column_list, [(single_row_subquery_expression), ...]
from from_tables
[where ...]

For example

Jika dijalankan maka hasilnya seperti berikut:

hr=# SELECT j.job_title,
hr-#        (SELECT min(h.start_date)::date FROM job_history h) as start_join
hr-# FROM jobs j;
            job_title            | start_join
---------------------------------+------------
 President                       | 1987-09-17
 Administration Vice President   | 1987-09-17
 Administration Assistant        | 1987-09-17
 Finance Manager                 | 1987-09-17
 Accountant                      | 1987-09-17
 Accounting Manager              | 1987-09-17
 Public Accountant               | 1987-09-17
 Sales Manager                   | 1987-09-17
 Sales Representative            | 1987-09-17
 Purchasing Manager              | 1987-09-17
 Purchasing Clerk                | 1987-09-17
 Stock Manager                   | 1987-09-17
 Stock Clerk                     | 1987-09-17
 Shipping Clerk                  | 1987-09-17
 Programmer                      | 1987-09-17
 Marketing Manager               | 1987-09-17
 Marketing Representative        | 1987-09-17
 Human Resources Representative  | 1987-09-17
 Public Relations Representative | 1987-09-17
(19 rows)

Khusus untuk SubQuery pada select column_list, tidak bisa menggunakan subquery yang menghasilkan data lebih dari 1 baris, contohnya seperti berikut:

select j.job_title,
       (select h.start_date from job_history h)
from jobs j;

Maka jika di jalankan hasilnya seperti berikut:

hr=# select j.job_title,
hr-#        (select h.start_date from job_history h)
hr-# from jobs j;
ERROR:  more than one row returned by a subquery used as an expression

Dan juga tidak bisa menggunakan subquery yang mehasilkan lebih dari 1 kolom, contohnya seperti berikut:

select j.job_title,
       (select h.start_date, h.start_date from job_history h limit 1)
from jobs j;

Jika di jalankan hasilnya seperti berikut:

hr=# select j.job_title,
hr-#        (select h.start_date, h.start_date from job_history h limit 1)
hr-# from jobs j;
ERROR:  subquery must return only one column
LINE 2:        (select h.start_date, h.start_date from job_history h...
               ^

Maka dari itu, kita harus memastikan data yang dikembalikan oleh subquery 1 row dan 1 column.

Correlate SubQuery

Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query.

PostgreSQL executes the query that contains a subquery in the following sequence:

Get a candiate row from outer query
executes the inner query using candidate row value
use value from inner query to quality or disqulity candidate row

Basic statement is:

SELECT column1, column2, ....
FROM table1 outer
WHERE column1 operator
                    (SELECT column3
                     FROM table2 inner
                     WHERE inner.column1 = outer.column1);

For example

Jika kita jalankan maka hasilnya seperti berikut:

hr=# select emp.employee_id,
hr-#        emp.first_name                           employee_name,
hr-#        (select man.first_name
hr(#         from employees man
hr(#         where emp.manager_id = man.employee_id) manager_name
hr-# from employees emp
hr-# where emp.manager_id is not null
hr-# limit 10;
 employee_id | employee_name | manager_name
-------------+---------------+--------------
         101 | Neena         | Steven
         102 | Lex           | Steven
         103 | Alexander     | Lex
         104 | Bruce         | Alexander
         105 | David         | Alexander
         106 | Valli         | Alexander
         107 | Diana         | Alexander
         108 | Nancy         | Neena
         109 | Daniel        | Nancy
         110 | John          | Nancy
(10 rows)

Jika temen-temen perhatikan pada subquery dengan where clause emp.manager_id = man.employee_id kita menggunakan column manager_id pada outer query. Dan jika kita mau ngambil data ke dua dari subquery, kita harus mendefinisikan column baru seperti berikut:

Jika dijalankan maka hasilnya seperti berikut:

hr=# select emp.employee_id,
hr-#        emp.first_name                           employee_name,
hr-#        emp.salary                               employee_salary,
hr-#        (select man.first_name
hr(#         from employees man
hr(#         where emp.manager_id = man.employee_id) manager_name,
hr-#        (select man.salary
hr(#         from employees man
hr(#         where emp.manager_id = man.employee_id) manager_salary
hr-# from employees emp
hr-# where emp.manager_id is not null
hr-# limit 10;
 employee_id | employee_name | employee_salary | manager_name | manager_salary
-------------+---------------+-----------------+--------------+----------------
         101 | Neena         |        17000.00 | Steven       |       24000.00
         102 | Lex           |        17000.00 | Steven       |       24000.00
         103 | Alexander     |         9000.00 | Lex          |       17000.00
         104 | Bruce         |         6000.00 | Alexander    |        9000.00
         105 | David         |         4800.00 | Alexander    |        9000.00
         106 | Valli         |         4800.00 | Alexander    |        9000.00
         107 | Diana         |         4200.00 | Alexander    |        9000.00
         108 | Nancy         |        12000.00 | Neena        |       17000.00
         109 | Daniel        |         9000.00 | Nancy        |       12000.00
         110 | John          |         8200.00 | Nancy        |       12000.00
(10 rows)

Using SubQuery inline view

The subquery specified in the FROM clause of a query is called an inline view. Because an inline view can replace a table in a query, it is also called a derived table. Sometimes, you may hear the term subselect, which is the same meaning as the inline view.

An inline view is not a real view but a subquery in the FROM clause of a SELECT statement. The basic syntax:

SELECT column1, column2, ....
FROM table1 outer, ( subquery_expression ) as subquery_alias, ...

For example:

Jika kita jalankan maka hasilnya seperti berikut:

hr=# select emp.employee_id, emp.first_name, emp.salary, func.rata2, func.minimun, func.maximum
hr-# from employees emp,
hr-#      (select round(avg(job.max_salary), 0) rata2,
hr(#              min(job.max_salary)           minimun,
hr(#              max(job.max_salary)           maximum
hr(#       from jobs job) as func
hr-# where emp.salary >= func.rata2;
 employee_id | first_name |  salary  | rata2 | minimun | maximum
-------------+------------+----------+-------+---------+---------
         100 | Steven     | 24000.00 | 13211 |    5000 |   40000
         101 | Neena      | 17000.00 | 13211 |    5000 |   40000
         102 | Lex        | 17000.00 | 13211 |    5000 |   40000
         145 | John       | 14000.00 | 13211 |    5000 |   40000
         146 | Karen      | 13500.00 | 13211 |    5000 |   40000

Lateral SubQueries

Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) For example:

select emp.employee_id,
       emp.first_name,
       history.job_id,
       history.start_date
from employees emp,
     (select job.job_id, job.start_date
      from job_history job
      where emp.employee_id = job.employee_id) as history

Jika kita execute maka hasilnya seperti berikut:

hr=# select emp.employee_id,
hr-#        emp.first_name,
hr-#        history.job_id,
hr-#        history.start_date
hr-# from employees emp,
hr-#      (select job.job_id, job.start_date
hr(#       from job_history job
hr(#       where emp.employee_id = job.employee_id) as history;
ERROR:  invalid reference to FROM-clause entry for table "emp"
LINE 8:       where emp.employee_id = job.employee_id) as history;
                    ^
HINT:  There is an entry for table "emp", but it cannot be referenced from this part of the query.

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

The basic syntax is:

SELECT column1, column2, ....
FROM table1 outer, LATERAL ( subquery_expression ) as subquery_alias, ...

When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set’s values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

For example:

Jika dijalankan hasilnya seperti berikut:

hr=# select emp.employee_id,
hr-#        emp.first_name,
hr-#        history.job_id,
hr-#        history.start_date
hr-# from employees emp,
hr-#      lateral (select job.job_id, job.start_date
hr(#               from job_history job
hr(#               where emp.employee_id = job.employee_id) as history
hr-# order by employee_id, job_id
hr-# ;
 employee_id | first_name |   job_id   | start_date
-------------+------------+------------+------------
         101 | Neena      | AC_ACCOUNT | 1989-09-21
         101 | Neena      | AC_MGR     | 1993-10-28
         102 | Lex        | IT_PROG    | 1993-01-13
         114 | Den        | ST_CLERK   | 1998-03-24
         122 | Payam      | ST_CLERK   | 1999-01-01
         176 | Jonathon   | SA_MAN     | 1999-01-01
         176 | Jonathon   | SA_REP     | 1998-03-24
         200 | Jennifer   | AC_ACCOUNT | 1994-07-01
         200 | Jennifer   | AD_ASST    | 1987-09-17
         201 | Michael    | MK_REP     | 1996-02-17
(10 rows)

Query tersebut akan sama jika kita menggunakan join seperti berikut:

select emp.employee_id,
       emp.first_name,
       history.job_id,
       history.start_date
from employees emp
         join job_history history on (emp.employee_id = history.employee_id)
order by employee_id, job_id;

SubQuery as predicate in where clause

Selanjutnya kita bahas SubQuery Expression atau SubQuery yang diletakan pada WHERE clause dengan beberapa operators atau predicates. Basic form of SubQuery:

select column_list, ...
from table1, ...
where column_expression operator (subquery_expression)

For example usage is:

Jika dijalankan hasilnya seperti berikut:

hr=# select employee_id, first_name, salary, commission_pct
hr-# from employees
hr-# where salary >= (
hr(#     select min(max_salary)
hr(#     from jobs
hr(#     where job_id = 'IT_PROG'
hr(# )
hr-# limit 10;
 employee_id | first_name |  salary  | commission_pct
-------------+------------+----------+----------------
         100 | Steven     | 24000.00 |
         101 | Neena      | 17000.00 |
         102 | Lex        | 17000.00 |
         108 | Nancy      | 12000.00 |
         114 | Den        | 11000.00 |
         145 | John       | 14000.00 |           0.40
         146 | Karen      | 13500.00 |           0.30
         147 | Alberto    | 12000.00 |           0.30
         148 | Gerald     | 11000.00 |           0.30
         149 | Eleni      | 10500.00 |           0.20
(10 rows)

Selain itu juga kita bisa gunakan correlate SubQuery pada WHERE clause seperti berikut:

Jika di jalankan hasilnya seperti berikut:

hr=# select employee_id, first_name, salary, commission_pct, job_id
hr-# from employees emp
hr-# where emp.salary = (
hr(#     select avg(min_salary)
hr(#     from jobs job
hr(#     where emp.job_id = job.job_id
hr(# );
 employee_id | first_name | salary  | commission_pct |  job_id
-------------+------------+---------+----------------+----------
         119 | Karen      | 2500.00 |                | PU_CLERK
         182 | Martha     | 2500.00 |                | SH_CLERK
         191 | Randall    | 2500.00 |                | SH_CLERK
(3 rows)

Kemudian, untuk operator yang kita bisa gunakan dalam SubQuery as Predicate terdiri dari behavior suatu subquery resultset (Single / Multiple Rows). Diantaranya

Using Single-Row Comparison
Using EXISTS operator
Using IN predicate to handle multiple values
Using ANY & SOME predicate to handle multiple values
Using ALL predicate to handler multiple values

Ok sekarang kita bahas satu-per-satu ya function tersebut.

Using SubQuery for Single-Row Comparison

SubQuery dengan WHERE clause pada Single Row Comparison pada dasarnya udah kita bahas di materi sebelumnya kita bisa menggunakan beberapa operator seperti

Relational predicate
Like predicates
Between predicates
Null predicate
Logical predicate
Regular Expression (Regex) predicate

The basic form:

select column_list, ...
from table1, ...
where column_expression [| (columns_expression)] operator ( subquery_expression )

Contohnya seperti berikut:

Jika di jalankan maka hasilnya seperti berikut:

hr=# select employee_id, first_name, salary
hr-# from employees emp
hr-# where emp.salary >= (select avg(max_salary) from jobs)
hr-# order by salary;
 employee_id | first_name |  salary
-------------+------------+----------
         146 | Karen      | 13500.00
         145 | John       | 14000.00
         101 | Neena      | 17000.00
         102 | Lex        | 17000.00
         100 | Steven     | 24000.00
(5 rows)

Selain itu juga kita bisa menggunakan multiple columns, contohnya seperti berikut:

Jika di jalankan hasilnya seperti berikut:

hr=# select employee_id, first_name, salary, coalesce(commission_pct, 0), job_id
hr-# from employees emp
hr-# where (emp.salary, emp.salary, emp.job_id) >= (
hr(#     select round(stddev(max_salary), 0), round(max(min_salary)), 'IT_PROG'
hr(#     from jobs
hr(# )
hr-# order by salary
hr-# limit 10;
 employee_id | first_name |  salary  | coalesce |   job_id
-------------+------------+----------+----------+------------
         158 | Allan      |  9000.00 |     0.35 | SA_REP
         109 | Daniel     |  9000.00 |        0 | FI_ACCOUNT
         152 | Peter      |  9000.00 |     0.25 | SA_REP
         103 | Alexander  |  9000.00 |        0 | IT_PROG
         163 | Danielle   |  9500.00 |     0.15 | SA_REP
         151 | David      |  9500.00 |     0.25 | SA_REP
         157 | Patrick    |  9500.00 |     0.35 | SA_REP
         170 | Tayler     |  9600.00 |     0.20 | SA_REP
         150 | Peter      | 10000.00 |     0.30 | SA_REP
         156 | Janette    | 10000.00 |     0.35 | SA_REP
(10 rows)

Pada query tersebut, jika kita menggunakan logical operator seperti berikut:

where salary >= 8876 or salary >= 20000 or job_id = IT_PROG

Using `EXISTS` operator

The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is “true”; if the subquery returns no rows, the result of EXISTS is “false”.

EXISTS (subquery)

For example

Jika dijalankan maka hasilnya seperti berikut:

hr=# select employee_id, first_name, job_id, salary
hr-# from employees out
hr-# where exists(
hr(#         select 1
hr(#         from job_history
hr(#         where employee_id = out.employee_id);
 employee_id | first_name | job_id  |  salary
-------------+------------+---------+----------
         176 | Jonathon   | SA_REP  |  8600.00
         101 | Neena      | AD_VP   | 17000.00
         114 | Den        | PU_MAN  | 11000.00
         200 | Jennifer   | AD_ASST |  4400.00
         201 | Michael    | MK_MAN  | 13000.00
         102 | Lex        | AD_VP   | 17000.00
         122 | Payam      | ST_MAN  |  7900.00
(7 rows)

Using `IN` predicate to handle multiple values

Untuk menghandle resultset multiple rows yang di kembalikan oleh SubQuery salah satu method yang paling commons adalah menggunakan IN predicate. Secara syntax seperti berikut:

expression IN (subquery)

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of IN is “true” if any equal subquery row is found. The result is “false” if no equal row is found (including the case where the subquery returns no rows).

Contoh penggunaanya seperti berikut:

Jika dijalankan maka hasilnya seperti berikut:

hr=# select employee_id, first_name, salary, job_id
hr-# from employees out
hr-# where job_id in (
hr(#     select distinct job_id
hr(#     from employees inq
hr(#     where inq.department_id = 80)
hr-# limit 10;
 employee_id | first_name  |  salary  | job_id
-------------+-------------+----------+--------
         145 | John        | 14000.00 | SA_MAN
         146 | Karen       | 13500.00 | SA_MAN
         147 | Alberto     | 12000.00 | SA_MAN
         148 | Gerald      | 11000.00 | SA_MAN
         149 | Eleni       | 10500.00 | SA_MAN
         150 | Peter       | 10000.00 | SA_REP
         151 | David       |  9500.00 | SA_REP
         152 | Peter       |  9000.00 | SA_REP
         153 | Christopher |  8000.00 | SA_REP
         154 | Nanette     |  7500.00 | SA_REP
(10 rows)

Sama halnya seperti sebelumnya, pada IN predicate juga bisa menggunakan multiple column seperti berikut contohnya:

Jika di jalankan maka hasilnya seperti berikut:

hr=# select employee_id, first_name, salary, job_id
hr-# from employees out
hr-# where (job_id, salary) in (
hr(#     select distinct job_id, (select max(min_salary) from jobs where inq.job_id = job_id)
hr(#     from employees inq
hr(# )
hr-# limit 10;
 employee_id | first_name | salary  |  job_id
-------------+------------+---------+----------
         119 | Karen      | 2500.00 | PU_CLERK
         182 | Martha     | 2500.00 | SH_CLERK
         191 | Randall    | 2500.00 | SH_CLERK

Untuk query tersebut jika kita menggunakan logical operator maka querynya seperti berikut:

where job_id = 'PU_CLERK' and job_id = 'SH_CLERK' and salary = 2500 and ...

Using `ANY` & `SOME` predicate to handle multiple values

Sama halnya dengan IN predicates, ANY atau SOME bisa digunakan untuk menghandle subquery untuk multiple rows. Berikut syntax dasarnya:

expression operator ANY (subquery)
expression operator SOME (subquery)

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ANY is “true” if any true result is obtained. The result is “false” if no true result is found (including the case where the subquery returns no rows).

SOME is a synonym for ANY. IN is equivalent to = ANY. As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.

row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)

For example

Selain itu juga kita bisa menggunakan > ANY atau < ANY seperti berikut:

Jika di jalankan maka hasilnya seperti berikut:

hr=# select max(salary) max_salary
hr-#     from employees
hr-#     group by job_id
hr-#     order by max_salary;
 max_salary
------------
    3100.00
    3600.00
    4200.00
    4400.00
    6000.00
    6500.00
    8200.00
    8300.00
    9000.00
    9000.00
   10000.00
   11000.00
   11500.00
   12000.00
   12000.00
   13000.00
   14000.00
   17000.00
   24000.00
(19 rows)

hr=# select employee_id, first_name, salary, job_id
hr-# from employees out
hr-# where salary = any (
hr(#     select max(salary) max_salary
hr(#     from employees
hr(#     group by job_id
hr(#     order by max_salary
hr(# )
hr-# limit 10;
 employee_id | first_name |  salary  |   job_id
-------------+------------+----------+------------
         100 | Steven     | 24000.00 | AD_PRES
         101 | Neena      | 17000.00 | AD_VP
         102 | Lex        | 17000.00 | AD_VP
         103 | Alexander  |  9000.00 | IT_PROG
         104 | Bruce      |  6000.00 | IT_PROG
         107 | Diana      |  4200.00 | IT_PROG
         108 | Nancy      | 12000.00 | FI_MGR
         109 | Daniel     |  9000.00 | FI_ACCOUNT
         110 | John       |  8200.00 | FI_ACCOUNT
         114 | Den        | 11000.00 | PU_MAN
(10 rows)

hr=# select employee_id, first_name, salary, job_id
hr-# from employees out
hr-# where salary > any (
hr(#     select max(salary) max_salary
hr(#     from employees
hr(#     group by job_id
hr(#     order by max_salary
hr(# )
hr-# limit 10;
 employee_id | first_name |  salary  |   job_id
-------------+------------+----------+------------
         100 | Steven     | 24000.00 | AD_PRES
         101 | Neena      | 17000.00 | AD_VP
         102 | Lex        | 17000.00 | AD_VP
         103 | Alexander  |  9000.00 | IT_PROG
         104 | Bruce      |  6000.00 | IT_PROG
         105 | David      |  4800.00 | IT_PROG
         106 | Valli      |  4800.00 | IT_PROG
         107 | Diana      |  4200.00 | IT_PROG
         108 | Nancy      | 12000.00 | FI_MGR
         109 | Daniel     |  9000.00 | FI_ACCOUNT

Using `ALL` predicate to handler multiple values

Dan yang terakhir, untuk menghandle multiple row pada predicate subquery yaitu ALL. Basic syntaxnya:

expression operator ALL (subquery)

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result using the given operator, which must yield a Boolean result. The result of ALL is “true” if all rows yield true (including the case where the subquery returns no rows). The result is “false” if any false result is found. The result is NULL if no comparison with a subquery row returns false, and at least one comparison returns NULL.

NOT IN is equivalent to <> ALL. As with EXISTS, it’s unwise to assume that the subquery will be evaluated completely.

row_constructor operator ALL (subquery)

For example:

Jika di jalankan hasilnya seperti berikut:

hr=# select min(salary) min_salary
hr-#     from employees
hr-#     group by job_id
hr-#     order by min_salary;
 min_salary
------------
    2100.00
    2500.00
    2500.00
    4200.00
    4400.00
    5800.00
    6000.00
    6100.00
    6500.00
    6900.00
    8300.00
   10000.00
   10500.00
   11000.00
   12000.00
   12000.00
   13000.00
   17000.00
   24000.00
(19 rows)

hr=# select employee_id, first_name, salary, job_id
hr-# from employees out
hr-# where salary <> ALL (
hr(#     select min(salary) min_salary
hr(#     from employees
hr(#     group by job_id
hr(#     order by min_salary
hr(# );
 employee_id | first_name  |  salary  |   job_id
-------------+-------------+----------+------------
         103 | Alexander   |  9000.00 | IT_PROG
         105 | David       |  4800.00 | IT_PROG
         106 | Valli       |  4800.00 | IT_PROG
         109 | Daniel      |  9000.00 | FI_ACCOUNT
         110 | John        |  8200.00 | FI_ACCOUNT
         111 | Ismael      |  7700.00 | FI_ACCOUNT
         112 | Jose Manuel |  7800.00 | FI_ACCOUNT
         115 | Alexander   |  3100.00 | PU_CLERK
         116 | Shelli      |  2900.00 | PU_CLERK
         117 | Sigal       |  2800.00 | PU_CLERK
         118 | Guy         |  2600.00 | PU_CLERK
         120 | Matthew     |  8000.00 | ST_MAN
         121 | Adam        |  8200.00 | ST_MAN
         122 | Payam       |  7900.00 | ST_MAN
         125 | Julia       |  3200.00 | ST_CLERK
         126 | Irene       |  2700.00 | ST_CLERK
         127 | James       |  2400.00 | ST_CLERK
(73 rows)

Selain itu juga kita bisa menggunakan > ALL atau < ALL seperti berikut:

Jika dijalankan hasilnya seperti berikut:

hr=# select max(salary) max_salary
hr-#     from employees
hr-#     group by job_id
hr-#     order by max_salary;
 max_salary
------------
    3100.00
    3600.00
    4200.00
    4400.00
    6000.00
    6500.00
    8200.00
    8300.00
    9000.00
    9000.00
   10000.00
   11000.00
   11500.00
   12000.00
   12000.00
   13000.00
   14000.00
   17000.00
   24000.00
(19 rows)

hr=# select employee_id, first_name, salary, job_id
hr-# from employees out
hr-# where salary < ALL (
hr(#     select max(salary) max_salary
hr(#     from employees
hr(#     group by job_id
hr(#     order by max_salary
hr(# );
 employee_id | first_name | salary  |  job_id
-------------+------------+---------+----------
         116 | Shelli     | 2900.00 | PU_CLERK
         117 | Sigal      | 2800.00 | PU_CLERK
         118 | Guy        | 2600.00 | PU_CLERK
         119 | Karen      | 2500.00 | PU_CLERK
         126 | Irene      | 2700.00 | ST_CLERK
         127 | James      | 2400.00 | ST_CLERK
         128 | Steven     | 2200.00 | ST_CLERK
         130 | Mozhe      | 2800.00 | ST_CLERK
         131 | James      | 2500.00 | ST_CLERK
         132 | TJ         | 2100.00 | ST_CLERK
         134 | Michael    | 2900.00 | ST_CLERK
         135 | Ki         | 2400.00 | ST_CLERK
         136 | Hazel      | 2200.00 | ST_CLERK
         139 | John       | 2700.00 | ST_CLERK
         140 | Joshua     | 2500.00 | ST_CLERK
         143 | Randall    | 2600.00 | ST_CLERK
         144 | Peter      | 2500.00 | ST_CLERK
         182 | Martha     | 2500.00 | SH_CLERK
         183 | Girard     | 2800.00 | SH_CLERK
         187 | Anthony    | 3000.00 | SH_CLERK
         190 | Timothy    | 2900.00 | SH_CLERK
         191 | Randall    | 2500.00 | SH_CLERK
         195 | Vance      | 2800.00 | SH_CLERK
         197 | Kevin      | 3000.00 | SH_CLERK
         198 | Donald     | 2600.00 | SH_CLERK
         199 | Douglas    | 2600.00 | SH_CLERK
(26 rows)

Share on

Twitter Facebook LinkedIn

Dimas Maryanto

Queries inside a query (subquery)

What is SubQuery?

Using SubQuery specified in `select_column_list`

Correlate SubQuery

Using SubQuery inline view

Lateral SubQueries

SubQuery as predicate in where clause

Using SubQuery for Single-Row Comparison

Using `EXISTS` operator

Using `IN` predicate to handle multiple values

Using `ANY` & `SOME` predicate to handle multiple values

Using `ALL` predicate to handler multiple values

Share on

You may also enjoy

Silabus Ansible: Pemula sampai Mahir

Getting started: Create and run your first Gitlab CI/CD pipeline

Settings up Gitlab CI (on-premise)

Introduction of Gitlab DevSecOps Platform

Dimas Maryanto

What is SubQuery?

Using SubQuery specified in select_column_list

Correlate SubQuery

Using SubQuery inline view

Lateral SubQueries

SubQuery as predicate in where clause

Using SubQuery for Single-Row Comparison

Using EXISTS operator

Using IN predicate to handle multiple values

Using ANY & SOME predicate to handle multiple values

Using ALL predicate to handler multiple values

Share on

You may also enjoy

Silabus Ansible: Pemula sampai Mahir

Getting started: Create and run your first Gitlab CI/CD pipeline

Settings up Gitlab CI (on-premise)

Introduction of Gitlab DevSecOps Platform

Using SubQuery specified in `select_column_list`

Using `EXISTS` operator

Using `IN` predicate to handle multiple values

Using `ANY` & `SOME` predicate to handle multiple values

Using `ALL` predicate to handler multiple values