SQLite - Select Data

To select data from an SQLite database, use the SELECT statement.

When you use this statement, you specify which table/s to select data from, as well as the columns to return from the query.

You can also provide extra criteria to further narrow down the data that is returned.

Simple SELECT Statement

We've already used a SELECT statement previously, when we inserted data.

The statement we used was this:

This is quite self-explanatory — it is telling SQLite to select all columns from the Artists table. The asterisk (*) is a shorthand way of saying "all columns". It saves us writing out the names of all the columns.

Therefore, this query returns all records and all columns. Like this:


1|Joe Satriani

2|Steve Vai

3|The Tea Party

4|Noiseworks

5|Wayne Jury

6|Mr Percival

7|Iron Maiden

8|Atmasphere

9|Ian Moss

10|Magnum

11|Strapping Young Lad

12|Slayer

13|Primus

14|Pat Metheny

15|Frank Gambale

16|Frank Zappa

17|The Wiggles

Formatting

Let's format the output so that our results are a bit easier to read.

Use Columns

You can use .mode to change the output mode. The above example uses .mode list, which displays the results as a list.

Let's change the mode to use columns.

When you do this, you may need to adjust the column widths (any data that is too wide for the column will be truncated).

Adjust Column Widths

To adjust the column widths, use the .width command, followed by widths for each column.

The following example sets the first column to 12 and the second column to 20.

Add Headers

You can also use .headers to specify whether or not to display column headers.

To display headers, use this:

You can remove them with .headers off.

Show Settings

You can review these, and other settings at any time, by typing .show


sqlite> .show

     echo: off

      eqp: off

  explain: off

  headers: on

     mode: column

nullvalue: ""

   output: stdout

separator: "|"

    stats: off

    width: 12 20 

New Format

Here's what the previous example looks like using columns and headers mode:


ArtistId    ArtistName  

----------  ------------

1           Joe Satriani

2           Steve Vai   

3           The Tea Part

4           Noiseworks  

5           Wayne Jury  

6           Mr Percival 

7           Iron Maiden 

8           Atmasphere  

9           Ian Moss    

10          Magnum      

11          Strapping Yo

12          Slayer      

13          Primus      

14          Pat Metheny 

15          Frank Gambal

16          Frank Zappa 

17          The Wiggles 

The WHERE Clause

You can use the WHERE clause to narrow the results down. The clause allows you to stipulate specific criteria for which to apply to the query.

For example, you could specify that only records, where a certain field contains a certain value should be returned.

Here's a very specific example. It specifies that only the record with an ArtistId equal to 6 should be returned:

Result:


ArtistId      ArtistName          

------------  --------------------

6             Mr Percival   

To find all artists whose name begins with the letter "S", you could do this:

Result:


ArtistId      ArtistName          

------------  --------------------

2             Steve Vai           

11            Strapping Young Lad 

12            Slayer     

The percent sign (%) is a wildcard character that can be used to specify any character. So, in this example, we are specifying that the artist's name must be like this pattern (i.e. the pattern starting with the letter S and following with any other character).

Another example of using the WHERE clause is to retrieve a range of values. For example, we could look for records where the value is less than a number, greater than a number, or within a certain range.

Here's an example of selecting only those records with an ArtistId less than a number:

Result:


ArtistId      ArtistName          

------------  --------------------

1             Joe Satriani        

2             Steve Vai           

3             The Tea Party       

4             Noiseworks          

5             Wayne Jury 

Here's an example of selecting only those records with an ArtistId within a certain range:

Result:


ArtistId      ArtistName          

------------  --------------------

5             Wayne Jury          

6             Mr Percival         

7             Iron Maiden         

8             Atmasphere          

9             Ian Moss            

10            Magnum   

Selecting Columns

You can specify that only certain columns are returned in the result set. Simply write the column names in the query. Multiple column names need to be separated by a comma.

It is good practice to select ony those columns you need. Using *, while convenient, can cause extra overhead if it returns more columns than is required.

So let's run the same query again, but this time, only select the ArtistName column:

Result:


ArtistName  

------------

Wayne Jury  

Mr Percival 

Iron Maiden 

Atmasphere  

Ian Moss    

Magnum 

The ORDER BY Clause

You can use the ORDER BY clause to limit the number of records returned.

You can add ASC for ascending order, or DESC for descending order. If you don't add anything, it will use ascending.

Here, we order by the ArtistName field in ascending order:

Result:


ArtistId      ArtistName          

------------  --------------------

8             Atmasphere          

15            Frank Gambale       

16            Frank Zappa         

9             Ian Moss            

7             Iron Maiden         

1             Joe Satriani        

10            Magnum              

6             Mr Percival         

4             Noiseworks          

14            Pat Metheny         

13            Primus              

12            Slayer              

2             Steve Vai           

11            Strapping Young Lad 

3             The Tea Party       

17            The Wiggles         

5             Wayne Jury  

And if we switch to descending order:

Result:


ArtistId      ArtistName          

------------  --------------------

5             Wayne Jury          

17            The Wiggles         

3             The Tea Party       

11            Strapping Young Lad 

2             Steve Vai           

12            Slayer              

13            Primus              

14            Pat Metheny         

4             Noiseworks          

6             Mr Percival         

10            Magnum              

1             Joe Satriani        

7             Iron Maiden         

9             Ian Moss            

16            Frank Zappa         

15            Frank Gambale       

8             Atmasphere  

The LIMIT Clause

You can use the LIMIT clause to limit the number of records returned. This can be handy if your table contains a large number of records but you only want to see a handful.

Here, we limit the record set to only five records:

Result:


ArtistId      ArtistName          

------------  --------------------

1             Joe Satriani        

2             Steve Vai           

3             The Tea Party       

4             Noiseworks          

5             Wayne Jury