Sorting, Unions, and Intersections

Howard's photo
Howard
·

2 min read

Sorting, Unions, and Intersections

1) Sorting

Sorting is one of the easiest things that we’re going to deal with in PostgreSQL.

To perform sorting we need to use the ORDER BY keyword.

We can sort by number, and by string.

The default order in sorting is ascending.

But we can change that by using DESC keyword.

For instance

SELECT * FROM products
ORDER BY price DESC;

We can also apply other sorting variations

To better illustrate, let’s imagine we want to sort with product price by ascending order, but with product weight we want to be in descending order.

We can do that by

SELECT * FROM products
ORDER BY price, weight DESC;

Offset and Limit

Offset

For instance, we have a user table

Currently, this table have 30 records (Rows)

But we only interested in the last 10 rows.

Offset can help us achieve that

We can skip the first 20 ones.

The result of this query will show from user 21 → 30.

SELECT * FROM users OFFSET 20;

Limit

It’s exactly what its name says.

Let’s continue with our above example

We already select the last 10.

Now, for example we only want to get the first 5 users, and drop the last 5.

Here’s what we will do.

SELECT * FROM users OFFSET 20 LIMIT 5;

Limit by itself

SELECT * FROM users LIMIT 5;

The query will try to get the first 5 users. With id from 1 → 5;

Usages

  • You might use LIMIT by itself when you want to find the TOP of something

  • LIMIT and OFFSET usually being used together

  • We use them to limit the amount of data we show to the user.

2) Union and Intersect

  • Union will take the result of one query and join it together with the result of the second query

Commonalities with Intersect

  • UNION → Join together the result of two queries and remove duplicate rows

  • UNION ALL → Join together the result of 2 queries, the result will contain duplicate.

  • INTERSECT → Find the common rows in the result of 2 queries, remove duplicates

  • INTERSECT ALL → Find the common rows in the result of 2 queries, the result will contain duplicates.

  • EXCEPT → Find the rows that present in the first query, but NOT in the second, and remove duplicates.

  • EXCEPT ALL → Find the rows that are present in the first query, but NOT in the second, the result will contain duplicates.

DatabasesSQLPostgreSQL