How to use clauses when querying data (FROM, WHERE, GROUP BY, HAVING, BETWEEN, LIKE, FETCH)

13 Agent

Arun Gavhane Senior Systems Engineer

SUMMARY: This article discusses the use of clauses in PostgreSQL queries to filter and organize data in results. It introduces different clauses and provides examples of their usage.

1. FROM clause

2. WHERE clause

3. GROUP BY clause

4. HAVING clause  

5. BETWEEN operator

6. LIKE condition

7. FETCH clause

 

Clauses are helpful and often necessary components of a search query to help filter and organize data. Let’s take a look at some of the most common clauses and how you can make best use of them when querying data in PostgreSQL.

FROM clause 

The FROM clause is used to specify the table or tables that data is retrieved from..

Example 

This example shows that all the columns can be retrieved using the FROM clause. Using the asterisk symbol (*) with SELECT will list all the columns in their defined order: 

postgres=# select * from emp;

 empno | ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno 

-------+--------+-----------+------+---------------------+---------+---------+--------

  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |         |     20

  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30

  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30

  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20

  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30

  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30

  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10

  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |         |     20

  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10

  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30

  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |         |     20

  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30

  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20

  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |         |     10

(14 rows)

 

To restrict the data to be retrieved from a table to specific columns, specify them after the SELECT keyword:

postgres=# select empno,ename from emp;

 empno | ename  

-------+--------

  7369 | SMITH

  7499 | ALLEN

  7521 | WARD

  7566 | JONES

  7654 | MARTIN

  7698 | BLAKE

  7782 | CLARK

  7788 | SCOTT

  7839 | KING

  7844 | TURNER

  7876 | ADAMS

  7900 | JAMES

  7902 | FORD

  7934 | MILLER

(14 rows)

 

WHERE clause 

The WHERE clause is used to restrict the data to rows that match specific conditions. 

Example

This example lists only the data of the employees who are working in the department number 10.

postgres=# select empno, ename, deptno from emp where deptno=10;

 empno | ename  | deptno 

-------+--------+--------

  7782 | CLARK  |     10

  7839 | KING   |     10

  7934 | MILLER |     10

(3 rows)

 

Character strings and dates can also be used in a WHERE clause.

Example

postgres=# select empno,ename,deptno from emp where ename='KING';

 empno | ename | deptno 

-------+-------+--------

  7839 | KING  |     10

(1 row)

 

GROUP BY clause

The rows in a table can be condensed into a smaller number groups that share the same value by using the GROUP BY clause.

Example 

This example displays departments with the average salary for their employees:

select deptno, avg(sal) from emp group by deptno;

 deptno |          avg          

--------+-----------------------

     20 | 2175.0000000000000000

     30 | 1566.6666666666666667

     10 | 2916.6666666666666667

(3 rows)

 

NOTE: All columns in the list that are not in grouped functions must be included in the GROUP BY clause.

 

HAVING clause

Results of a GROUP BY clause can be restricted to those satisfying certain conditions using the HAVING clause. The WHERE clause cannot be used to restrict groups.

Example

Attempting to use WHERE and GROUP BY together will give an error: 

postgres=# select deptno, AVG(sal) from emp where AVG(sal) > 300 group by deptno;

ERROR:  aggregate functions are not allowed in WHERE

LINE 1: select deptno, AVG(sal) from emp where AVG(sal) > 300 group ...

                                               ^



postgres=# select deptno, AVG(sal) from emp group by deptno having AVG(sal) > 300;

 deptno |          avg          

--------+-----------------------

     10 | 2916.6666666666666667

     20 | 2175.0000000000000000

     30 | 1566.6666666666666667

(3 rows)

 

BETWEEN operator

The BETWEEN operator can be used to find out the range between two values.

Example 

Here BETWEEN is used to look for salaries with a value greater than or equal to 500 and less than or equal to 1000:

postgres=# select ename,sal from emp where sal between 500 and 1000;

 ename |  sal   

-------+--------

 SMITH | 800.00

 JAMES | 950.00

(2 rows)

 

Example

This example sorts and displays the results between two character values: 

postgres=# select empno,ename from emp order by ename;

 empno | ename  

-------+--------

  7876 | ADAMS

  7499 | ALLEN

  7698 | BLAKE

  7782 | CLARK

  7902 | FORD

  7900 | JAMES

  7566 | JONES

  7839 | KING

  7654 | MARTIN

  7934 | MILLER

  7788 | SCOTT

  7369 | SMITH

  7844 | TURNER

  7521 | WARD

(14 rows)



postgres=# select ename,sal from emp where ename between 'ALLEN' and 'JONES';

 ename |   sal   

-------+---------

 ALLEN | 1600.00

 JONES | 2975.00

 BLAKE | 2850.00

 CLARK | 2450.00

 FORD  | 3000.00

 JAMES |  950.00

(6 rows)

 

LIKE condition

The LIKE condition is used to match a character pattern in wild-card searches.

• % denotes zero or many characters. 

• _ denotes one character.

 

Example

This example lists the employees with names starting with letter S. The ‘%’ below denotes zero or many characters:

postgres=# select ename from emp where ename like 'S%';

 ename 

-------

 SCOTT

 SMITH

(2 rows)

 

Example

This example lists the employee names whose second letter is the letter L. The ‘_’ below denotes one character only:

postgres=# select ename from emp where ename like '_L%';

 ename 

-------

 ALLEN

 BLAKE

 CLARK

(3 rows)

 

FETCH clause

The FETCH clause is used to limit the number of retrieved portions of rows generated by a query. 

NOTE: You should use the FETCH clause with the ORDER BY clause to get a consistent result, as the order of rows stored in a table is unpredictable.

postgres=# select ename,sal from emp order by sal ;

 ename  |   sal   

--------+---------

 SMITH  |  800.00

 JAMES  |  950.00

 ADAMS  | 1100.00

 MARTIN | 1250.00

 WARD   | 1250.00

 MILLER | 1300.00

 TURNER | 1500.00

  ALLEN  | 1600.00

 CLARK  | 2450.00

 BLAKE  | 2850.00

 JONES  | 2975.00

 FORD   | 3000.00

 SCOTT  | 3000.00

 KING   | 5000.00

(14 rows)

 

Example

This example sorts the results based on the salary column and displays only the first row/record from the query:

postgres=# select ename,sal from emp order by sal 

           FETCH FIRST ROW ONLY;

 ename |  sal   

-------+--------

 SMITH | 800.00

(1 row)

 

OR

postgres=# select ename,sal from emp order by sal '

           FETCH FIRST 1 ROW ONLY;

 ename |  sal   

-------+--------

 SMITH | 800.00

(1 row)

 

Example

This example sorts the results based on the salary column and displays the first three rows only from the query:

postgres=# select ename,sal from emp order by sal 

           FETCH FIRST 3 ROW ONLY;

 ename |   sal   

-------+---------

 SMITH |  800.00

 JAMES |  950.00

 ADAMS | 1100.00

(3 rows)

 

You can either use the above syntax or the below syntax.

postgres=# select ename,sal from emp order by sal 

  FETCH NEXT 3 ROW ONLY;

 ename |   sal   

-------+---------

 SMITH |  800.00

 JAMES |  950.00

 ADAMS | 1100.00

(3 rows)

 

Example

This example sorts the results based on the salary column and displays the second five rows from the query by adding the OFFSET clause before FETCH:

postgres=# select ename,sal from emp order by sal 

           OFFSET 5 rows 

           FETCH FIRST 5 ROW ONLY;

 ename  |   sal   

--------+---------

 MILLER | 1300.00

 TURNER | 1500.00

 ALLEN  | 1600.00

 CLARK  | 2450.00

 BLAKE  | 2850.00

(5 rows)

 

 

Arun GavhaneSenior Systems Engineer