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;