login

Subscribe to PostgreSQL Guide websiteRSS Feed
 » PostgreSQL Guide » Retrieving Data by Using Simple SQL Select Statement

Retrieving Data by using SELECT Statement

In order to retrieve data from database tables you can use Select statement. In the Select statement you have to specify at least two things:

  • The table where you want to retrieve data.
  • and the data or selection list you want to retrieve.

Here is the simple SELECT statement:

SELECT col1,col2,... 
FROM tbl

If you want to select more than one column you have to seperate them by a comma. To retrieve all column from a table you can use shorthand artistic (*) like following:

SELECT * FROM tbl

To retrieve all customers from the customer table you can use the following query:

SELECT * FROM customer

Normally when you retrieve data from a database table, the column heading name is the name of column name. You can also assign a new column heading by using keyword AS. In PostgreSQL AS keyword is mandatory.

SELECT col1 AS col1_alias, col2 AS col2_alias   
FROM tbl

If you only want to retrieve the first name and last name of the customer and give it to a meaningful heading you can use the following query:

 SELECT customer_id AS id,   
        fname AS firstname,  
        lname AS lastname  
 FROM customer

To eliminate the duplicated row in the result set which you retrieve from a datable table you can use keyword DISTINCT as follows:

SELECT DISTINCT col1   
FROM tbl  
  
SELECT DISTINCT col1, col2   
FROM tbl
  • In the first statement PostgreSQL eliminates all duplicated rows which has the same value in col1 column.
  • In the second statement PostgreSQL eliminates all duplicated rows which are combined by col1 and col2 column.

The visual explaination of SQL distinct is as follows:

SQL DISTINCT

Suppose you want to retrieve unique last name of customer you can use DISTINCT as follows:

SELECT DISTINCT lname  
FROM customer

Sometimes you want to restrict the returned result set by your own criteria. In this case you can use WHERE clause as follows:

SELECT col1,col2   
FROM tbl   
WHERE conditions

Conditions can be any expression which restricts the returned result set. For example if you want to retrieve all customers which has title is Mr. you can use the following query:

SELECT customer_id,title, fname, lname  
FROM customer  
WHERE title = 'Mr'

Somtimes you want to sort the result base on your own criteria in ascending or desceding fashion, in this case you can use ORDER BY with the keywords ASC (ascending) or DESC (descending) to do so.

SELECT col1,col2,..  
FROM tbl  
WHERE conditions   
ORDER BY col1 ASC, col2 DESC

For example you can retrieve all customers which have title is Mrs and then you sort them by first name in ascesding order and last name in descending order.

SELECT customer_id,title, fname, lname  
FROM customer  
WHERE title = 'Mrs'  
ORDER BY fname ASC, lname DESC

SQL ORDER BY