Querying Data (SELECT)
The SELECT statement is used to retrieve data from tables. In this chapter, we'll learn about the various clauses commonly used in DuckDB queries.
Retrieving All Data
To retrieve all rows and columns from a table, use the asterisk (*):
SELECT * FROM students;
The result:
| id | name | major | intake_year |
|---|---|---|---|
| 1 | Alice | Computer Science | 2023 |
| 2 | Bob | Information Systems | 2022 |
| 3 | Carol | Computer Science | 2023 |
Retrieving Specific Columns
You can pick which columns to retrieve:
SELECT name, major FROM students;
Filtering Data with WHERE
The WHERE clause is used to limit the returned rows based on a condition:
SELECT * FROM students
WHERE intake_year = 2023;
The result:
| id | name | major | intake_year |
|---|---|---|---|
| 1 | Alice | Computer Science | 2023 |
| 3 | Carol | Computer Science | 2023 |
Some operators you can use in WHERE:
| Operator | Description | Example |
|---|---|---|
= |
Equal to | WHERE intake_year = 2023 |
!= or <> |
Not equal to | WHERE major != 'Information Systems' |
> |
Greater than | WHERE id > 1 |
< |
Less than | WHERE id < 3 |
>= |
Greater than or equal to | WHERE intake_year >= 2022 |
<= |
Less than or equal to | WHERE intake_year <= 2023 |
AND |
Both conditions must be true | WHERE intake_year = 2023 AND major = 'Computer Science' |
OR |
One of the conditions is true | WHERE major = 'Computer Science' OR major = 'Information Systems' |
NOT |
Negates a condition | WHERE NOT intake_year = 2022 |
Sorting Results with ORDER BY
The ORDER BY clause is used to sort the query results:
SELECT * FROM students
ORDER BY name;
For descending order, add DESC:
SELECT * FROM students
ORDER BY intake_year DESC;
Removing Duplicates with DISTINCT
The DISTINCT clause is used to return only unique values:
SELECT DISTINCT major FROM students;
The result:
| major |
|---|
| Computer Science |
| Information Systems |
Limiting the Number of Rows with LIMIT
The LIMIT clause is used to limit the number of rows returned:
SELECT * FROM students
LIMIT 5;
Combining Clauses
You can combine several clauses in a single query:
SELECT name, major
FROM students
WHERE intake_year = 2023
ORDER BY name
LIMIT 10;
Summary
In this chapter, we learned how to retrieve data with SELECT, filter with WHERE, sort with ORDER BY, remove duplicates with DISTINCT, and limit the results with LIMIT.
In the next chapter, we'll learn how to combine data from multiple tables using JOIN.