In this article we will be looking into the basic use of PostgreSQL SELECT statement to query data from the database table. For the sake of this article we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link.
The SELECT statement is as complex and flexible as it can get for a query statement. It can be used with various clauses which increases it’s flexibility and use cases to query data from a table.
The various clauses that can be used with the SELECT statement are listed below:
- DISTINCT operator: It is used to select distinct rows from a table.
- ORDER BY clause: It is used to sort table rows.
- WHERE clause: It is used to filter rows from a table.
- >LIMIT clause: It is used to select a subset of rows from the table.
- FETCH clause: It is also used to select subset of rows from the table.
- GROUP BY clause: It is used to group different rows into a single group.
- HAVING clause: It is used to filter rows from a table with specified attributes or features.
- FROM clause: It is used to specify a column in a table.
- joins: It is used to join two or more tables together using joins such as INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses.
- Set operators: These operators such as UNION, INTERSECT, and EXCEPT are used to manipulate the different sets of data.
For the sake of simplicity we will be looking into the use of SELECT statement with FROM clause in our sample DVD rental database.
The syntax for using the SELECT statement is as follows:
Syntax: SELECT select_list FROM table_name;
Now, let’s evaluate the SELECT statement in more detail,
- Firstly, we need to specify a particular column or a list of columns from where the data is to be retrieved. Commas are used to separate multiple columns, if a list of column is selected as below:
SELECT select_list1, select_list2, select_list3 FROM table_name;
For selecting all columns from the database, use asterisk(‘*’) as below:
SELECT * FROM table_name;
- Secondly, we need to specify the name of the table from which data is to be retrieved, after the FROM keyword.
Now let us look into a few examples of using SELECT statement in our sample database:
Example 1:
Using SELECT statement to query data from one column
SELECT first_name FROM customer;
Output:
Example 2:
Using SELECT statement to query data from multiple columns
SELECT first_name, email FROM customer;
Output:
Example 3:
Using SELECT statement to query data in all columns of a table
SELECT * FROM customer;
Output:
Example 4:
Using SELECT statement with expressions
SELECT first_name || ' ' || last_name AS full_name, email FROM customer;
Output: