1. Querying Data
Select
It is used to query data from tables using
SELECT
statementIt has following clauses
Select distinct rows using
DISTINCT
classSort rows using
ORDER BY
clauseFilter rows using
WHERE
clauseSelect subset of rows from table using
LIMIT
orFETCH
classGroup rows into groups using
GROUP BY
clauseFilter groups using
HAVING
clauseJoin with other tables using joins such as
INNTER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN
clausesPerform set operations using
UNION, INTERSECT, EXCEPT
clauses
PostgreSQL evaluates the
FROM
clause before theSELECT
clause in the SELECT statementWe 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
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
Example 2
Order By
It is used to sort the rows of the result set, we use it with the
SELECT
clauseWe can get the results in
ASC
orDESC
order, it usesASC
by defaultPostgreSQL evaluates the clauses in the SELECT statment in the following order:
Example 1
Example 2
NULL
Clause
NULL
ClauseIn 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
orNULLS LAST
option ofORDER BY
clause
Select Distinct
DISTINCT
clause is used to remove duplicate rows from a result setIt 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
Example 2
DISTINCT ON
PostgreSQL also provides the
DISTINCT ON (expression)
to keep the “first” row of each group of duplicatesThe 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
Last updated