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

 

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: