انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
منشورBEG-050-L04

SELECT وWHERE وORDER BY

  • الدرس 4 من 9
  • 10 دقيقة قراءة
  • مبتدئ
في هذا الدرس 11 موضوعات

هذا الدرس متاح حاليًا باللغة الإنجليزية فقط، ولم تُنشر نسخته العربية بعد.

Lesson Overview

Until now you have used SELECT only to peek at a table. This lesson makes it a precise tool: choose the columns, filter the rows with WHERE, sort them with ORDER BY, and keep only the first few with LIMIT. It is also where you meet NULL, the value that trips up almost every beginner.

In the library picture, this is the main skill of the module: asking the librarian a precise question. "Show me the books" gets you the whole catalog. "Show me titles and years of books published after 1990, newest first, but only five" gets you an answer you can use.

Nothing here changes data, so you can experiment freely. All queries in this lesson, and the rest of the module, run on a small seeded library: 5 authors, 11 books, 5 members, and 8 loans.

This lesson builds on Lessons 1 to 3 (SQL families, the library tables, and the WHERE habit).

Learning Objectives

By the end of this lesson you will be able to:

  • Select all columns (*) or a chosen list of columns, with aliases.
  • Filter rows with comparisons, AND, OR, IN, and LIKE, using parentheses correctly.
  • Sort by one or more columns, ascending or descending.
  • Limit the number of rows returned and explain why LIMIT needs ORDER BY.
  • Explain NULL and test for it with IS NULL.
  • Predict the result of a query on a small table.

Written Explanation

The shape of a query

sql
SELECT columns
FROM table
WHERE condition
ORDER BY columns
LIMIT n;

You write the clauses in this order. Only SELECT and FROM are required. Conceptually the database first finds the rows in the table (FROM), keeps those that pass WHERE, then picks the columns (SELECT), sorts (ORDER BY), and cuts (LIMIT). That order explains several rules below.

Choosing columns

  • SELECT * returns every column. It is handy for exploring; in real code name the columns you need, so the result does not change when someone adds a column.
  • SELECT title, published_year FROM books; returns just those two, in that order.
  • AS gives a column a different name in the result: SELECT title AS book FROM books;.

Filtering with WHERE

WHERE keeps the rows for which the condition is true.

OperatorMeaningExample
= <>equal, not equalauthor_id = 2, published_year <> 1974
< <= > >=comparisonspublished_year > 1990
AND, ORcombine conditionsauthor_id = 1 AND published_year < 1970
IN (...)matches any value in a listauthor_id IN (1, 4)
LIKEtext pattern: % is any run of characterstitle LIKE 'The%'

(The manual lists != as an alias for <>. This course uses <>.) Text and dates are written in single quotes: joined_on >= '2024-01-01'. In PostgreSQL, LIKE is case-sensitive; ILIKE ignores case, but that is a PostgreSQL extension.

AND binds tighter than OR. a OR b AND c means a OR (b AND c). When you mix them, add parentheses to say what you mean, as Example 2 shows.

NULL: "unknown"

NULL marks a missing or unknown value, such as the publication year of an anonymous poem or the return date of a book still out. It is not zero and not an empty string. Because it means "unknown", comparing anything to it gives "unknown", not true. The PostgreSQL manual says that 7 = NULL yields null, and tells you not to write expression = NULL. Test for it with:

sql
WHERE returned_on IS NULL        -- rows with no value
WHERE returned_on IS NOT NULL    -- rows with a value

A consequence: a condition like published_year <> 1974 is also "unknown" for a NULL year, so those rows are not returned. If NULL rows matter, say so in the condition.

Sorting with ORDER BY

sql
ORDER BY published_year          -- ascending (smallest first) is the default
ORDER BY published_year DESC     -- descending (largest first)
ORDER BY author_id, title DESC   -- by author_id ascending, ties broken by title descending

With several columns, later ones only break ties among rows equal in the earlier ones, and each column has its own direction. In PostgreSQL, NULL sorts as if larger than any value, so it comes last in ascending order and first in descending order (NULLS FIRST or NULLS LAST can override this).

Without ORDER BY, the order of the rows is not guaranteed. The manual says that it must not be relied on. A table that "looks sorted" today may not look sorted tomorrow. If order matters, say so.

Limiting rows with LIMIT

LIMIT 5 keeps at most five rows; OFFSET 3 skips the first three before counting. LIMIT is not part of the core SQL standard (PostgreSQL, MySQL, and SQLite support it, and the standard has its own FETCH FIRST syntax; as in Lesson 1, check your product). The PostgreSQL manual says that you should use ORDER BY with LIMIT to get a predictable subset; otherwise the subset is unpredictable. "The top five" only means something once you have said "by what".

Examples

All results below are real output from PostgreSQL 18.3 on the seeded library.

Example 1: choosing, filtering, sorting, limiting (simple)

Books published after 1990, newest first, at most five:

sql
SELECT title, published_year
FROM books
WHERE published_year > 1990
ORDER BY published_year DESC
LIMIT 5;
text
        title         | published_year 
----------------------+----------------
 Lavinia              |           2008
 Fledgling            |           2005
 Parable of the Sower |           1993
(3 rows)

LIMIT 5 is a maximum: only three books qualify. Notice also that "Collected Verses", whose year is NULL, is not in the result: NULL > 1990 is not true.

Example 2: AND, OR, and parentheses (practical)

Two queries that differ only by parentheses:

sql
SELECT title, published_year FROM books
WHERE author_id = 2 OR author_id = 3 AND published_year < 1960
ORDER BY title;
text
       title       | published_year 
-------------------+----------------
 Arrow of God      |           1964
 Palace Walk       |           1956
 Things Fall Apart |           1958
(3 rows)

Read as author_id = 2 OR (author_id = 3 AND published_year < 1960): all of author 2's books, plus author 3's early ones, so Arrow of God (1964) sneaks in. If you wanted "authors 2 or 3, and published before 1960":

sql
SELECT title, published_year FROM books
WHERE (author_id = 2 OR author_id = 3) AND published_year < 1960
ORDER BY title;
text
       title       | published_year 
-------------------+----------------
 Palace Walk       |           1956
 Things Fall Apart |           1958
(2 rows)

The same condition could be written author_id IN (2, 3) AND published_year < 1960, which avoids the trap.

Example 3: the NULL trap on loans (realistic)

Which loans are still out? A book still out has no return date. First, the wrong way:

sql
SELECT id FROM loans WHERE returned_on = NULL;

This returns (0 rows). No error, no warning, and no rows, so a report built on it would say "nothing is overdue". The right way:

sql
SELECT id, loaned_on, due_on
FROM loans
WHERE returned_on IS NULL
ORDER BY due_on;
text
 id | loaned_on  |   due_on   
----+------------+------------
  2 | 2025-09-01 | 2025-09-15
  3 | 2025-09-20 | 2025-10-04
  5 | 2025-10-10 | 2025-10-24
  7 | 2025-10-20 | 2025-11-03
(4 rows)

Sorting by due_on puts the longest-outstanding loan first, which is the order a librarian would chase them in.

One more check on NULL in sorting. With ORDER BY published_year, "Collected Verses" appeared last; with DESC it appeared first, as the PostgreSQL manual describes.

Backend Insight

Almost every read your backend performs is a SELECT with a WHERE (find this user, list this customer's orders) and often an ORDER BY and LIMIT (newest first, one page at a time). The "page" in an API such as "orders, 20 per page" is usually built with ORDER BY plus LIMIT and OFFSET, so a stable, unique sort order is a correctness requirement, not decoration.

NULL behaviour is a classic source of production bugs: a filter that quietly drops rows with missing values, or a report that undercounts. Decide for each nullable column what "missing" should mean in your queries, and test with a row that has NULL.

Common Mistakes

Mistake 1: = NULL

  • Incorrect: WHERE returned_on = NULL
  • Correct: WHERE returned_on IS NULL

The first returns no rows and no error.

Mistake 2: Mixing AND and OR without parentheses

The result is valid SQL that answers a different question than you meant (Example 2). Parenthesize.

Mistake 3: Assuming a natural order

Rows came out "in id order" in your test, so you assumed they always will. Without ORDER BY, no order is promised.

Mistake 4: LIMIT without ORDER BY

"Top five" with no sort returns an arbitrary five.

Mistake 5: Forgetting quotes, or using the wrong ones

  • Incorrect: WHERE title = The Dispossessed or WHERE title = "The Dispossessed"
  • Correct: WHERE title = 'The Dispossessed'

A misspelled column fails loudly: SELECT titel FROM books; gave ERROR: column "titel" does not exist, with a hint suggesting books.title. Read the hint.

Exercises

Try each exercise yourself before opening the solutions. Use the seeded library if you have one; otherwise reason from the tables in the lesson.

Level A: Foundation

A1. Write a query that lists the name and joined_on of every member.

أظهر الحل A1

A1. SELECT name, joined_on FROM members;

A2. Write a query for books published in or after 1970 by author 1.

أظهر الحل A2

A2. SELECT title FROM books WHERE author_id = 1 AND published_year >= 1970;

A3. Which query finds members who joined in 2024 or later, newest first?

أظهر الحل A3

A3. SELECT name, joined_on FROM members WHERE joined_on >= '2024-01-01' ORDER BY joined_on DESC;

Level B: Application

B1. List the titles of books whose title contains the word of, sorted alphabetically. (On the seeded data, four titles match.)

أظهر الحل B1

B1. SELECT title FROM books WHERE title LIKE '%of%' ORDER BY title; The % on both sides means "anywhere in the title". (It also matches words that contain of inside them, such as a title with "Profile".)

B2. List loans that were returned late, as far as you can tell from one table: returned_on after due_on. Which loan ids do you expect, using the seeded data: loan 6 has due_on 2025-10-26 and returned_on 2025-10-30?

أظهر الحل B2

B2. SELECT id FROM loans WHERE returned_on > due_on; A NULL returned_on is excluded because the comparison is unknown. Loan 6 is returned 4 days late; with the seeded data it is the only match.

B3. Show the three oldest books by published_year, ignoring books with an unknown year.

أظهر الحل B3

B3. SELECT title, published_year FROM books WHERE published_year IS NOT NULL ORDER BY published_year LIMIT 3; (Without the filter, NULL sorts last in ascending order in PostgreSQL, so it would not appear in the first three anyway, but the explicit filter states your intention.)

Level C: Challenge

C1. Explain why SELECT title FROM books WHERE published_year <> 1974; returns 9 rows from a table of 11 books, where one book was published in 1974.

أظهر الحل C1

C1. The table has 11 books. One was published in 1974, so that is excluded by <>. A second book has a NULL year, and NULL <> 1974 is unknown, not true, so it is excluded too. That leaves 9.

C2. Write a query that returns the books with unknown year and the books published before 1960, in a single result, sorted by title. Say what the OR and IS NULL do.

أظهر الحل C2

C2. SELECT title FROM books WHERE published_year IS NULL OR published_year < 1960 ORDER BY title; IS NULL selects unknown years, < 1960 selects the early books (which never include NULL), and OR merges the two sets.

Mini Challenge

The librarian asks three questions. Write one query for each, run them if you have the seeded library, and note the number of rows.

  1. "Which members joined before 2025, in alphabetical order?"
  2. "Which are the two most recently published books, with their years?" (Think about the NULL year.)
  3. "Which loans have not been returned, oldest due date first?"

Check your work: every query has a WHERE or LIMIT where needed, query 2 states ORDER BY ... DESC and handles NULL (it sorts first when descending), and query 3 uses IS NULL and not = NULL.

Summary & Next Lesson

Key takeaways

  • SELECT columns FROM table WHERE condition ORDER BY columns LIMIT n; is the core query shape.
  • WHERE keeps rows whose condition is true; use parentheses when mixing AND and OR.
  • NULL means unknown: use IS NULL and IS NOT NULL. A comparison with NULL is never true.
  • ORDER BY defaults to ascending; add DESC for descending. Without it, order is not guaranteed.
  • LIMIT is meaningful only with ORDER BY.

Next lesson

You can now pull out individual rows. The next lesson, "Aggregates, GROUP BY, HAVING", summarizes many rows at once: counting, averaging, and grouping, and filtering on those summaries.

Self-check

  1. How do you test for a missing return date?
  2. What does a OR b AND c mean?
  3. Which comes first in ascending order in PostgreSQL: NULL or 5?
  4. Why add ORDER BY to a LIMIT query?
  5. What does LIKE 'The%' match?
أظهر الإجابات
  1. WHERE returned_on IS NULL.
  2. a OR (b AND c), because AND binds tighter.
  3. 5; NULL sorts last in ascending order by default.
  4. Without it, the rows that make up "the first N" are arbitrary.
  5. Any text that begins with The.

References

  • PostgreSQL documentation, Sorting Rows (ORDER BY). Supports ascending as the default, per-column directions, NULL ordering, and that unsorted order must not be relied on.
  • PostgreSQL documentation, LIMIT and OFFSET. Supports the syntax, the need for ORDER BY for predictable results, and OFFSET skipping first. That LIMIT is outside the core SQL standard is general knowledge that I could not confirm on a fetched page, so it is hedged in the text.
  • PostgreSQL documentation, Comparison Functions and Operators. Supports <> with != as an alias, comparisons with NULL yielding null, and IS NULL.
  • All query results were produced on PostgreSQL 18.3. The LIKE/ILIKE and AND-before-OR remarks were checked by running queries, not on a fetched page.

التطبيق

طبّق هذا الدرس بمختبر الوحدة وتمارينها واختبارها.

المختبر والتمارين الاختبار

تعليم الدرس كمكتمل اختياري ويمكنك التراجع عنه في أي وقت. يبقى تقدمك محفوظًا في هذا المتصفح.

تابع إلى الدرس التالي

اكتب للبحث في المستويات والمراحل والوحدات والدروس.