1. Querying Data

Select

  • It is used to query data from tables using SELECT statement

  • It has following clauses

    • Select distinct rows using DISTINCT class

    • Sort rows using ORDER BY clause

    • Filter rows using WHERE clause

    • Select subset of rows from table using LIMIT or FETCH class

    • Group rows into groups using GROUP BY clause

    • Filter groups using HAVING clause

    • Join with other tables using joins such as INNTER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses

    • Perform set operations using UNION, INTERSECT, EXCEPT clauses

  • PostgreSQL evaluates the FROM clause before the SELECT clause in the SELECT statement

  • We can also use SELECT statement with expression e.g

    • SELECT 5 * 3;

Concatenation Operator ||

We can use this operator to concatenate multiple columns together e.g

SELECT
	first_name || ' ' || last_name,
	email
FROM
	customer;

Column Aliases

  • It allows us to assign a column or expression in SELECT statement a temporary name

  • The AS keyword is optional.

  • Following are two styles of defining column aliases

Example 1

SELECT
	first_name "First Name",
	last_name Surname
FROM customer;

Example 2

SELECT
	first_name || ' ' || last_name AS full_name
FROM
	customer;

Order By

  • It is used to sort the rows of the result set, we use it with the SELECT clause

  • We can get the results in ASC or DESC order, it uses ASC by default

  • PostgreSQL evaluates the clauses in the SELECT statment in the following order:

Example 1

SELECT
	first_name,
	last_name
FROM
	customer
ORDER BY
	first_name ASC,
	last_name DESC;

Example 2

SELECT
	first_name,
	LENGTH(first_name) len
FROM
	customer
ORDER BY
	len DESC;

NULL Clause

  • In db, NULL is a marker that indicates missing or unknown data

  • When you sort rows that contains NULL, you can specify order of NULL with other non-null values using the NULLS FIRST or NULLS LAST option of ORDER BY clause


Select Distinct

  • DISTINCT clause is used to remove duplicate rows from a result set

  • It can be applied to one or more columns

  • If we specify multiple columns, DISTINCT clause will evaluate the duplicate based on the combination of values of columns given

Example 1

SELECT
	DISTINCT bcolor
FROM
	distinct_demo
ORDER BY
	bcolor;

Example 2

SELECT
	DISTINCT bcolor,
	fcolor
FROM
	distinct_demo
ORDER BY
	bcolor,
	fcolor;

DISTINCT ON

  • PostgreSQL also provides the DISTINCT ON (expression) to keep the “first” row of each group of duplicates

  • The order of rows returned from the SELECT statement is unspecified therefore the “first” row of each group of the duplicate is also unspecified.

  • It is a good practice to always use the ORDER BY clause with the DISTINCT ON(expression) to make the result set predictable.

  • Notice that the DISTINCT ON expression must match the leftmost expression in the ORDER BY clause.

Example 3

SELECT
	DISTINCT ON (bcolor) bcolor,
	fcolor
FROM
	distinct_demo
ORDER BY
	bcolor,
	fcolor;

Last updated