We will describes the SQL-compliant subquery expressions available in PostgreSQL

Below are the list

- EXISTS
- IN
- NOT IN
- ANY/SOME
- ALL

### EXISTS

EXISTS clause always use with 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”

i.e EXISTS subquery returns a boolean (True/False)

```
postgres=# select empno,deptno,empname from emp where exists (select 1 from dept);
empno | deptno | empname
-------+--------+----------
11 | 5 | Solomon
10 | 5 | Bill
12 | 5 | Susan
13 | 1 | Wendy
14 | 1 | Benjamin
15 | 1 | Tom
16 | 2 | Henry
17 | 2 | Robert
18 | 2 | Paul
(9 rows)
postgres=# select empno,deptno,empname from emp where exists (select 1 from dept where deptno = 10);
empno | deptno | empname
-------+--------+---------
(0 rows)
postgres=# select empno,deptno,empname from emp e where exists (select 1 from dept d where d.deptno = e.deptno);
empno | deptno | empname
-------+--------+----------
11 | 5 | Solomon
10 | 5 | Bill
12 | 5 | Susan
13 | 1 | Wendy
14 | 1 | Benjamin
15 | 1 | Tom
16 | 2 | Henry
17 | 2 | Robert
18 | 2 | Paul
(9 rows)
```

### IN

expression IN (subquery)

IN clause evaluates subquery into results and these results are passed on to main query. The right-hand side is a parenthesised subquery, which must return exactly one column

```
postgres=# select empno,empname from emp where deptno in (1,2);
empno | empname
-------+----------
13 | Wendy
14 | Benjamin
15 | Tom
16 | Henry
17 | Robert
18 | Paul
(6 rows)
postgres=# select empno,empname from emp where deptno in (select deptno from dept where location != 'Dubai' );
empno | empname
-------+---------
11 | Solomon
10 | Bill
12 | Susan
16 | Henry
17 | Robert
18 | Paul
(6 rows)
```

### NOT IN

expression NOT IN (subquery)

NOT IN clause evaluates subquery into results and these results are passed on to main query.

The right-hand side is a parenthesised subquery, which must return exactly one column.

```
postgres=# select empno,empname from emp where deptno not in (1,2);
empno | empname
-------+---------
11 | Solomon
10 | Bill
12 | Susan
(3 rows)
postgres=# select empno,empname from emp where deptno not in (select deptno from dept where location = 'Dubai' );
empno | empname
-------+---------
11 | Solomon
10 | Bill
12 | Susan
16 | Henry
17 | Robert
18 | Paul
(6 rows)
```

### ANY/SOME

expression operator ANY (subquery)

expression operator SOME (subquery)

SOME is a synonym for ANY.

IN is equivalent to = ANY

The right-hand side is a parenthesised 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.

```
postgres=# select empno,empname from emp where deptno = any (select deptno from dept where location = 'Dubai' );
empno | empname
-------+----------
13 | Wendy
14 | Benjamin
15 | Tom
(3 rows)
postgres=# select empno,empname from emp where deptno = some (select deptno from dept where location = 'Dubai' );
empno | empname
-------+----------
13 | Wendy
14 | Benjamin
15 | Tom
(3 rows)
```

### ALL

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.

```
postgres=# select empno,empname from emp where deptno = ALL (select deptno from dept where location = 'Dubai');
empno | empname
-------+----------
13 | Wendy
14 | Benjamin
15 | Tom
(3 rows)
```