Using SELECT to query data from a single table and multiple tables 

13 Agent

Hrishikesh Gore Systems Engineer

SUMMARY: This article covers the SELECT statement in PostgreSQL and how to use it to query data from single and multiple tables:

1. Overview for SELECT statement

2. Retrieving methods from the database

          1. From a single table

         2. From multiple tables

         3. Using standard joins

        4. Using subquery

 

Overview for the SELECT statement

PostgreSQL is based on the Relational Database Management System (RDBMS). Data can be stored in the following hierarchy :

Database Cluster

             -> Database/s

                       -> Schema/s

                                -> Table/s

To retrieve data from any specific table, we have to use the SELECT statement.

The SELECT statement can be divided into three main parts:

Syntax:

SELECT <list_of_columns> FROM <list_of_table_names> WHERE <conditions> ;

 

<list_of_columns>: specifies the column names that need to be retrieved from that particular table or tables.

<list_of_table_names>: the tables from which to retrieve the data.

<conditions>: optional parameters used for any further restrictions on the data.

Note: You can get sample table structures along with data which is used in throughout the article from the link below:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/11/Database_Compatibility_for_Oracle_Developers_Guide.1.011.html

 

Retrieving methods from database

From a single table 

To retrieve all the information from single table you can use the following format :

Syntax :

SELECT * FROM <table_name>;

 

For example :

SELECT * FROM emp;

 

Output:

 

To retrieve specific information from a single table, we can simply define the specific column names and apply conditions for the table :

We can use ‘AS’ for alias or used to assign temporarily a new name to a table column.

For example :

SELECT empno as employee_id, ename as employee_name, job as job_title 

FROM emp 

WHERE sal BETWEEN 1000 AND 5000;

 

Output :

 

From multiple tables

To retrieve information from more than one table, you need to join those tables together. This can be done using JOIN methods, or you can use a second SELECT statement inside your main SELECT query—a subquery.

Using Joins

Joins are used to retrieve rows from two or more tables, based on a related column between those tables.

Syntax :

SELECT <list_of_column_names> FROM <table1> , <table2> … <tableN> WHERE <conditions>;

 

For example :

SELECT e.empno as employee_id, e.ename as employee_name, d.dname as department_name, d.loc as location

FROM emp e , dept d

WHERE e.deptno=d.deptno AND e.deptno IN (10,30);

 

Output :

 

Using Subquery:

Syntax :

SELECT <list_of_column_names> 

FROM <table_name> 

WHERE <column_name> = (SELECT <column_name> FROM <table_name> WHERE <condition> );

 

For example :

select empno,ename,sal,deptno 

from emp 

where deptno=(select deptno from dept where loc='DALLAS');

 

Output:

 

Hrishikesh GoreSystems Engineer