# How to perform basic queries with `SELECT` in PostgreSQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. The `SELECT` command is the main way to query the data within tables and views in PostgreSQL. This guide demonstrates the basic syntax and operation of this highly flexible command. Canonical URL: https://www.prisma.io/dataguide/postgresql/reading-and-querying-data/basic-select Authors: Justin Ellingwood Content revision: 91b5ea3b95a0824bcd0e0d61f68d6121c6e45039fde2b5f874c4c65eec29030e Source revision: b6716699f6439c3ba6ae3bf7116b7da84963c978 ## Introduction The `SELECT` command is the primary way to query and read information about records stored within database tables within PostgreSQL. Its usefulness, however, is not restricted to [read-only operations](https://www.prisma.io/dataguide/intro/database-glossary#read-operation). The `SELECT` syntax is combined with many other commands to target specific records or fields within databases for updates, deletions, and more complex operations. In this guide, we'll show how the basic syntax of `SELECT` supports gathering data from tables. While we'll leave the vast number of optional clauses to the command for other articles, it will hopefully become evident how even the most basic components provide a strong foundation for querying data. These fundamentals only require you to learn a few clauses and constructions. ## The general syntax of the `SELECT` command The basic format of a `SELECT` command looks something like this: ```sql SELECT FROM ; ``` This statement is composed of a few different pieces: - `SELECT`: The `SELECT` command itself. This SQL statement indicates that we want to query tables or views for data they contains. The arguments and clauses surrounding it determine both the contents and the format of the output by defining criteria. - ``: The `SELECT` statement can return entire rows (indicated by the `*` wildcard character) or a subset of the available [columns](https://www.prisma.io/dataguide/intro/database-glossary#column). If you want to output only specific columns, provide the column names you'd like to display, separated by commas. - `FROM `: The `FROM` keyword is used to indicate the [table](https://www.prisma.io/dataguide/intro/database-glossary#table) or [view](https://www.prisma.io/dataguide/intro/database-glossary#view) that should be queried. In most simple queries, this consists of a single table that contains the data you're interested in. - ``: A large number of filters, output modifiers, and conditions can be specified as additions to the `SELECT` command. You can use these to help pinpoint data with specific properties, modify the output formatting, or further process the results. ## Specifying columns to display with `SELECT` The column specification portion of the `SELECT` command requires you to name the columns you want to display for the data you are querying. For ad hoc querying and during data exploration, one of the most helpful options is to use an asterisk to indicate that you want to display values from every column available: ```sql SELECT * FROM my_table; ``` This will display all of the records from `my_table` since we do not provide any filtering to narrow the results. All of the columns for each record will be shown in the order that they are defined within the table. You can also choose to view a subset of available column by specifying them by name. Column names are separated by commas and are displayed in the order in which they are given: ```sql SELECT column2, column1 FROM my_table; ``` This will display all of the records from `my_table`, but only show the columns named `column2` and `column1`, in that order. ## Using column aliases with `AS` to modify the resulting table You can optionally set _column aliases_ to modify the name used for columns in the output. ```sql SELECT column1 AS "first column" FROM my_table; ``` This will show the each of the values for `column1` in `my_table`. However, the column in the output will be labeled as `first column` instead of `column1`. This is especially useful if the output combines column names from multiple tables that might share names or if it includes computed columns that don't already have a name. ## Defining sort order with `ORDER BY` The `ORDER BY` clause can be used to sort the resulting rows according to the criteria given. The general syntax looks like this: ```sql SELECT * FROM my_table ORDER BY ; ``` This will display the values for all columns in all records within `my_table`. The results will be ordered according to the expression represented by the placeholder ``. For example, suppose we have a `customer` table that contains columns for `first_name`, `last_name`, `address`, and `phone_number`. If we want to display the results in alphabetical order by `last_name`, we could use the following command: ```sql SELECT * FROM customer ORDER BY last_name; ``` Output: ``` first_name | last_name | address | phone_number -----------+-----------+--------------+------------- sue | abed | 456 side st | 5557654321 rachael | coern | 789 other st | 5559876543 thomas | renault | 777 first st | 5555647382 john | smith | 123 main st | 5551234567 jane | smith | 123 main st | 5551234567 ``` The results are sorted in ascending alphabetical order by the `last_name` column. To reverse the ordering, we can add the `DESC` modifier to the end of the `ORDER BY` clause: ```sql SELECT * FROM customer ORDER BY last_name DESC; ``` Output: ``` first_name | last_name | address | phone_number -----------+-----------+--------------+------------- john | smith | 123 main st | 5551234567 jane | smith | 123 main st | 5551234567 thomas | renault | 777 first st | 5555647382 rachael | coern | 789 other st | 5559876543 sue | abed | 456 side st | 5557654321 ``` You can also sort by multiple columns. Here, we sort first by `last_name`, and then by `first_name` for any columns with the same `last_name` value. Both sorts are in ascending order: ```sql SELECT * FROM customer ORDER BY last_name, first_name; ``` Output: ``` first_name | last_name | address | phone_number -----------+-----------+--------------+------------- sue | abed | 456 side st | 5557654321 rachael | coern | 789 other st | 5559876543 thomas | renault | 777 first st | 5555647382 jane | smith | 123 main st | 5551234567 john | smith | 123 main st | 5551234567 ``` One additional option that is often important is clarifying where `NULL` values should be presented in the sort order. You can do this by adding `NULLS FIRST` (the default) or `NULLS LAST` for any sort column: ```sql SELECT * FROM customer ORDER BY last_name NULLS LAST; ``` ## Getting distinct results If you want to find the range of values for a column in PostgreSQL, you can use the `SELECT DISTINCT` variant. This will display a single row for each distinct value of a column. The basic syntax looks like this: ```sql SELECT DISTINCT column1 FROM my_table; ``` This will show one row per unique value in `column1`. For example, to display all of the different values for `color` that your `shirt` table contains, you can type: ```sql SELECT DISTINCT color FROM shirt; ``` ``` color ------ blue green orange red yellow ``` To show uniqueness across multiple columns, you can add additional columns separated by commas. For instance, this will display all of the different combinations of `color` and `shirt_size` for the `shirt` table: ```sql SELECT DISTINCT color,shirt_size FROM shirt; ``` ``` color | shirt_size -------+----------- blue | M blue | S green | M green | L green | S orange | L orange | M red | M yellow | S ``` This displays every unique combination of `color` and `shirt_size` within the table. An often more flexible variant is PostgreSQL's `SELECT DISTINCT ON` command. This format allows you to specify a list of columns that should be unique in combination and separately list the columns you wish to display. The general syntax looks like this, with the column or columns that should be unique listed in the parentheses after `SELECT DISTINCT ON`, followed by the columns you wish to display: ```sql SELECT DISTINCT ON (column1) column1, column2 FROM my_table ORDER BY column1; ``` For example, if you want to display a single color for each shirt size, you could type: ```sql SELECT DISTINCT ON (shirt_size) color,shirt_size FROM shirt; ``` ``` color | shirt_size ------+----------- red | M green | L green | S ``` This will show a single row for each unique value in `shirt_size`. For each row, it will display the `color` column, followed by the `shirt_size` column. If using an `ORDER BY` clause, the column selected for ordering must match the column selected within the `DISTINCT ON` parentheses for the output to have predictable results: ```sql SELECT DISTINCT ON (shirt_size) color,shirt_size FROM shirt ORDER BY shirt_size DESC; ``` ``` color | shirt_size ------+----------- green | S red | M green | L ``` ## Conclusion In this guide, we covered some of the basic ways you can use the `SELECT` command to identify and display records from your tables and views. The `SELECT` command is one of the most flexible and powerful operations within SQL-oriented databases, with many different ways to add clauses, conditions, and filtering. While we only covered basic usage in this guide, the general format you learned here will serve as the foundation for all other read and many write queries. Learning ways to filter and target the results more accurately extend the capabilities that we covered today.