> For the complete documentation index, see [llms.txt](https://notes.nomanaziz.me/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://notes.nomanaziz.me/development/database/sql/basics/1.-querying-data.md).

# 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

```sql
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

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

#### Example 2

```sql
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:
  \*

  ```
  <figure><img src="/files/EHqSDUEuzSG45HmeTOZN" alt=""><figcaption></figcaption></figure>
  ```

#### Example 1

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

<figure><img src="/files/AlwbGIuljCWnk6lTxjAf" alt=""><figcaption></figcaption></figure>

#### Example 2

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

<figure><img src="/files/Y8YqWPt3Sn7USbLqHDj4" alt=""><figcaption></figcaption></figure>

#### `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

```sql
SELECT
	DISTINCT bcolor
FROM
	distinct_demo
ORDER BY
	bcolor;
```

#### Example 2

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

<figure><img src="/files/QwoJuYlY0LkmnfcmgFLS" alt=""><figcaption></figcaption></figure>

#### 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

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

<figure><img src="/files/gA1Nl2l3mg5nR73Ko7GB" alt=""><figcaption></figcaption></figure>