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:
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:
