What is the SELECT query in SQL?

SELECT is the most frequently used command in SQL, across any SQL database management systems. It’s used to retrieve data from a database using the given conditions, or no condition at all, then returns data in a table structure called a result set.

Learning how to use SELECT query is probably the most fundamental knowledge of using queries in SQL.

Syntax

Here is the generic syntax for a SELECT statement:

SELECT column_names
FROM table_name
[conditions]
;

To SELECT all available columns, instead of writing every column name:

SELECT *
FROM table_name
[conditions]
;

The conditions will be specified in the SELECT statement to narrow the result set. Here are some of the most commonly used optional clauses:

  • WHERE specifies which rows to retrieve.
  • GROUP BY rolls up rows using a shared attribute so that an aggregate function can process each group.
  • HAVING selects among the groups defined by the GROUP BY clause.
  • ORDER BY specifies an order in which to return the rows.
  • AS provides an alias which can be used to temporarily rename tables or columns.

Example

We have a table Playlist with two columns PlaylistId and Name

PlaylistId Name
1 Music
2 Movies
3 TV Shows
4 Audiobooks
5 90’s Music
6 Audiobooks
7 Movies
8 Music
9 Music Videos
10 TV Shows
11 Brazilian Music
12 Classical
13 Classical 101 - Deep Cuts
14 Classical 101 - Next Steps
15 Classical 101 - The Basics
16 Grunge
17 Heavy Metal Classic
18 On-The-Go 1

You want to see only the records with PlaylistId higher than 10, use the SELECT statement:

SELECT
  *
FROM
  Playlist
WHERE
  PlaylistId > 10;

And it returns:

PlaylistId Name
11 Brazilian Music
12 Classical
13 Classical 101 - Deep Cuts
14 Classical 101 - Next Steps
15 Classical 101 - The Basics
16 Grunge
17 Heavy Metal Classic
18 On-The-Go 1

Or you want to see the PlaylistId of the record named Classical:

SELECT
  PlaylistId
FROM
  Playlist
WHERE
  "Name" = "Classical";

And the result is:

PlaylistId
12

Or you want to see data from the whole table Playlist:

SELECT * FROM Playlist;

The result is the data table.

PlaylistId Name
1 Music
2 Movies
3 TV Shows
4 Audiobooks
5 90’s Music
6 Audiobooks
7 Movies
8 Music
9 Music Videos
10 TV Shows
11 Brazilian Music
12 Classical
13 Classical 101 - Deep Cuts
14 Classical 101 - Next Steps
15 Classical 101 - The Basics
16 Grunge
17 Heavy Metal Classic
18 On-The-Go 1

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode