PostgreSQL Queries and Joins

Standard SQL queries work in PostgreSQL as they are. This lesson shows them together with the PostgreSQL features that make everyday queries easier.

The examples use the original six students and four courses from the introduction.

Case-Insensitive Search with ILIKE

In PostgreSQL, LIKE cares about capital letters: '%khan%' does not match Ali Khan. Use ILIKE to ignore case:

SELECT name, city
FROM students
WHERE name ILIKE '%khan%';

   name   |  city
----------+---------
 Ali Khan | Karachi
(1 row)

This is perfect for a search box, where users type in any case.

Joining Text

The || operator joins text:

SELECT name || ' from ' || city AS intro
FROM students
WHERE age < 20;

          intro
-------------------------
 Ali Khan from Karachi
 Hamza Raza from Karachi
(2 rows)

If any part is NULL, || gives NULL. CONCAT(...) treats NULL as empty text instead.

Other useful text functions: upper(), lower(), length(), trim(), left(name, 3), replace() and split_part('Ali Khan', ' ', 1), which returns Ali.

LIMIT and OFFSET

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 2 OFFSET 1;

      name       | marks
-----------------+-------
 Sana Iqbal      |    88
 Ayesha Siddiqui |    85
(2 rows)

This skips the top student and shows the 2nd and 3rd. The SQL standard form FETCH FIRST 2 ROWS ONLY also works.

Joins

All four join types are supported. Here is a FULL JOIN, which keeps every student and every course, matched where possible:

SELECT s.name, c.title
FROM students AS s
FULL JOIN courses AS c ON s.course_id = c.id
ORDER BY c.id, s.id;

      name       |       title
-----------------+--------------------
 Ali Khan        | Web Development
 Bilal Ahmed     | Web Development
 Ayesha Siddiqui | Python Programming
 Sana Iqbal      | Python Programming
 Fatima Noor     | Graphic Designing
                 | Digital Marketing
 Hamza Raza      |
(7 rows)

Hamza has no course and Digital Marketing has no students, but both appear. In PostgreSQL, NULLs come last when sorting ascending; add NULLS FIRST to change that.

STRING_AGG: Join Values from Many Rows

STRING_AGG is an aggregate that joins text from a group into one value:

SELECT c.title, STRING_AGG(s.name, ', ' ORDER BY s.name) AS students
FROM courses AS c
JOIN students AS s ON s.course_id = c.id
GROUP BY c.id, c.title
ORDER BY c.id;

       title        |          students
--------------------+-----------------------------
 Web Development    | Ali Khan, Bilal Ahmed
 Python Programming | Ayesha Siddiqui, Sana Iqbal
 Graphic Designing  | Fatima Noor
(3 rows)

FILTER: Count Only Some Rows

FILTER adds a condition to one aggregate, so you can count different things in one query:

SELECT c.title,
       COUNT(s.id) AS total,
       COUNT(s.id) FILTER (WHERE s.marks >= 80) AS high_scorers
FROM courses AS c
LEFT JOIN students AS s ON s.course_id = c.id
GROUP BY c.id, c.title
ORDER BY c.id;

       title        | total | high_scorers
--------------------+-------+--------------
 Web Development    |     2 |            0
 Python Programming |     2 |            2
 Graphic Designing  |     1 |            1
 Digital Marketing  |     0 |            0
(4 rows)

Window Functions

A window function calculates a value across related rows without grouping them into one. RANK() gives each student a position, here inside their own city:

SELECT name, city, marks,
       RANK() OVER (PARTITION BY city ORDER BY marks DESC) AS city_rank
FROM students
ORDER BY city, city_rank;

      name       |   city    | marks | city_rank
-----------------+-----------+-------+-----------
 Fatima Noor     | Islamabad |    92 |         1
 Ali Khan        | Karachi   |    78 |         1
 Hamza Raza      | Karachi   |    70 |         2
 Bilal Ahmed     | Karachi   |    64 |         3
 Sana Iqbal      | Lahore    |    88 |         1
 Ayesha Siddiqui | Lahore    |    85 |         2
(6 rows)

• PARTITION BY city restarts the ranking for each city
• ORDER BY marks DESC decides who is first

Other window functions include ROW_NUMBER(), DENSE_RANK(), LAG(), LEAD(), and SUM(...) OVER (...) for running totals.

Exercise