Querying Data

Teachers

Retrieving data is the most common job in a database. SQL gives you powerful tools to filter, sort, and combine data.

Shaping Your Results

Sorting (ORDER BY)

By default, the database returns rows in no specific order. Use ORDER BY to sort them.

-- Sort books by published year (oldest to newest)
SELECT * FROM books
ORDER BY published_year ASC;

-- Sort books by published year (newest to oldest)
SELECT * FROM books
ORDER BY published_year DESC;

-- Sort authors by last name, then first name
SELECT * FROM authors
ORDER BY last_name ASC, first_name ASC;

Limiting Results (LIMIT)

Useful if you only want the "Top 10" or for pagination (showing 10 items per page).

-- Get the 5 most recently published books
SELECT * FROM books
ORDER BY published_year DESC
LIMIT 5;

-- Pagination example: get results 11-20 (Skip 10, take 10)
-- This is useful when showing a "next page" of results.
SELECT * FROM books
ORDER BY published_year DESC
LIMIT 10 OFFSET 10;

JOINS: Combining Tables

This is the most important concept in SQL. Since we split our data into multiple tables, we need JOIN to bring them back together.

INNER JOIN

Returns only rows where there is a match in BOTH tables. If a student has no classes, they won't appear. This join is the one used by far the most

-- Get a list of books and the author who wrote each one
-- INNER JOIN returns only rows where there is a match in BOTH tables.
SELECT
    b.title,
    b.published_year,
    a.first_name || ' ' || a.last_name AS author
FROM books b
INNER JOIN authors a ON a.id = b.author_id;

Note: We join books to authors using books.author_id = authors.id. This links each book to the author who wrote it.

LEFT JOIN (or LEFT OUTER JOIN)

This is here only so you know it exists. While it has its uses, you will be using inner joins for the vast majority of your queries, so we are not going to cover this in this week.

Returns ALL rows fsrom the "Left" table (the one mentioned first), and matching rows from the "Right" table. If there is no match, the columns from the right table will be NULL (empty).

-- Get ALL authors, and their books if they have any.
-- If an author has no books, the book columns will be NULL.
SELECT
    a.first_name,
    a.last_name,
    b.title,
    b.published_year
FROM authors a
LEFT JOIN books b ON b.author_id = a.id;

Which one to use?

Use INNER JOIN when you only want complete records (e.g., only students who are actually enrolled).
Use LEFT JOIN when you want to list everything from the main table, even if it has no matches (e.g., a list of all students, even those not in a class). Needed a lot less than you would expect.

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

CC BY-NC-SA 4.0 Icons

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.