# Differences between a relational (SQL) database and DynamoDB when reading data from a table
<a name="SQLtoNoSQL.ReadData"></a>

With SQL, you use the `SELECT` statement to retrieve one or more rows from a table. You use the `WHERE` clause to determine the data that is returned to you.

This is different than using Amazon DynamoDB which provides the following operations for reading data:
+ `ExecuteStatement` retrieves a single or multiple items from a table. `BatchExecuteStatement` retrieves multiple items from different tables in a single operation. Both of these operations use [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html), a SQL-compatible query language.
+ `GetItem` – Retrieves a single item from a table. This is the most efficient way to read a single item because it provides direct access to the physical location of the item. (DynamoDB also provides the `BatchGetItem` operation, allowing you to perform up to 100 `GetItem` calls in a single operation.)
+ `Query` – Retrieves all of the items that have a specific partition key. Within those items, you can apply a condition to the sort key and retrieve only a subset of the data. `Query` provides quick, efficient access to the partitions where the data is stored. (For more information, see [Partitions and data distribution in DynamoDB](HowItWorks.Partitions.md).)
+ `Scan` – Retrieves all of the items in the specified table. (This operation should not be used with large tables because it can consume large amounts of system resources.)

**Note**  
With a relational database, you can use the `SELECT` statement to join data from multiple tables and return the results. Joins are fundamental to the relational model. To ensure that joins run efficiently, the database and its applications should be performance-tuned on an ongoing basis. DynamoDB is a non-relational NoSQL database that does not support table joins. Instead, applications read data from one table at a time. 

The following sections describe different use cases for reading data, and how to perform these tasks with a relational database and with DynamoDB.

**Topics**
+ [

# Differences in reading an item using its primary key
](SQLtoNoSQL.ReadData.SingleItem.md)
+ [

# Differences in querying a table
](SQLtoNoSQL.ReadData.Query.md)
+ [

# Differences in scanning a table
](SQLtoNoSQL.ReadData.Scan.md)

# Differences in reading an item using its primary key
<a name="SQLtoNoSQL.ReadData.SingleItem"></a>

One common access pattern for databases is to read a single item from a table. You have to specify the primary key of the item you want.

**Topics**
+ [

## Reading an item using its primary key with SQL
](#SQLtoNoSQL.ReadData.SingleItem.SQL)
+ [

## Reading an item using its primary key in DynamoDB
](#SQLtoNoSQL.ReadData.SingleItem.DynamoDB)

## Reading an item using its primary key with SQL
<a name="SQLtoNoSQL.ReadData.SingleItem.SQL"></a>

In SQL, you would use the `SELECT` statement to retrieve data from a table. You can request one or more columns in the result (or all of them, if you use the `*` operator). The `WHERE` clause determines which rows to return.

The following is a `SELECT` statement to retrieve a single row from the *Music* table. The `WHERE` clause specifies the primary key values.

```
SELECT *
FROM Music
WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today'
```

You can modify this query to retrieve only a subset of the columns.

```
SELECT AlbumTitle, Year, Price
FROM Music
WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today'
```

Note that the primary key for this table consists of *Artist* and *SongTitle*.

## Reading an item using its primary key in DynamoDB
<a name="SQLtoNoSQL.ReadData.SingleItem.DynamoDB"></a>

In Amazon DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to read an item from a table.

------
#### [ DynamoDB API ]

With the DynamoDB API, you use the `PutItem` operation to add an item to a table.

DynamoDB provides the `GetItem` operation for retrieving an item by its primary key. `GetItem` is highly efficient because it provides direct access to the physical location of the item. (For more information, see [Partitions and data distribution in DynamoDB](HowItWorks.Partitions.md).)

By default, `GetItem` returns the entire item with all of its attributes.

```
{
    TableName: "Music",
    Key: {
        "Artist": "No One You Know",
        "SongTitle": "Call Me Today"
    }
}
```

You can add a `ProjectionExpression` parameter to return only some of the attributes.

```
{
    TableName: "Music",
    Key: {
        "Artist": "No One You Know",
        "SongTitle": "Call Me Today"
    },
    "ProjectionExpression": "AlbumTitle, Year, Price"
}
```

Note that the primary key for this table consists of *Artist* and *SongTitle*.

The DynamoDB `GetItem` operation is very efficient. It uses the primary key values to determine the exact storage location of the item in question, and retrieves it directly from there. The SQL `SELECT` statement is similarly efficient, in the case of retrieving items by primary key values.

The SQL `SELECT` statement supports many kinds of queries and table scans. DynamoDB provides similar functionality with its `Query` and `Scan` operations, which are described in [Differences in querying a table](SQLtoNoSQL.ReadData.Query.md) and [Differences in scanning a table](SQLtoNoSQL.ReadData.Scan.md).

The SQL `SELECT` statement can perform table joins, allowing you to retrieve data from multiple tables at the same time. Joins are most effective where the database tables are normalized and the relationships among the tables are clear. However, if you join too many tables in one `SELECT` statement application performance can be affected. You can work around such issues by using database replication, materialized views, or query rewrites.

DynamoDB is a nonrelational database and doesn't support table joins. If you are migrating an existing application from a relational database to DynamoDB, you need to denormalize your data model to eliminate the need for joins.

------
#### [ PartiQL for DynamoDB ]

With PartiQL, you use the `ExecuteStatement` operation to read an item from a table, using the PartiQL `Select` statement.

```
SELECT AlbumTitle, Year, Price
FROM Music
WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today'
```

Note that the primary key for this table consists of Artist and SongTitle. 

**Note**  
 The select PartiQL statement can also be used to Query or Scan a DynamoDB table

For code examples using `Select` and `ExecuteStatement`, see [PartiQL select statements for DynamoDB](ql-reference.select.md).

------

# Differences in querying a table
<a name="SQLtoNoSQL.ReadData.Query"></a>

Another common access pattern is reading multiple items from a table, based on your query criteria.

**Topics**
+ [

## Querying a table with SQL
](#SQLtoNoSQL.ReadData.Query.SQL)
+ [

## Querying a table in DynamoDB
](#SQLtoNoSQL.ReadData.Query.DynamoDB)

## Querying a table with SQL
<a name="SQLtoNoSQL.ReadData.Query.SQL"></a>

When using SQL the `SELECT` statement lets you query on key columns, non-key columns, or any combination. The `WHERE` clause determines which rows are returned, as shown in the following examples.

```
/* Return a single song, by primary key */

SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today';
```

```
/* Return all of the songs by an artist */

SELECT * FROM Music
WHERE Artist='No One You Know';
```

```
/* Return all of the songs by an artist, matching first part of title */

SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle LIKE 'Call%';
```

```
/* Return all of the songs by an artist, only if the price is less than 1.00 */

SELECT * FROM Music
WHERE Artist='No One You Know'
AND Price < 1.00;
```

Note that the primary key for this table consists of *Artist* and *SongTitle*.

## Querying a table in DynamoDB
<a name="SQLtoNoSQL.ReadData.Query.DynamoDB"></a>

In Amazon DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to query an item from a table.

------
#### [ DynamoDB API ]

With Amazon DynamoDB, you can use the `Query` operation to retrieve data in a similar fashion. The `Query` operation provides quick, efficient access to the physical locations where the data is stored. For more information, see [Partitions and data distribution in DynamoDB](HowItWorks.Partitions.md).

You can use `Query` with any table or secondary index. You must specify an equality condition for the partition key's value, and you can optionally provide another condition for the sort key attribute if it is defined.

The `KeyConditionExpression` parameter specifies the key values that you want to query. You can use an optional `FilterExpression` to remove certain items from the results before they are returned to you.

In DynamoDB, you must use `ExpressionAttributeValues` as placeholders in expression parameters (such as `KeyConditionExpression` and `FilterExpression`). This is analogous to the use of *bind variables* in relational databases, where you substitute the actual values into the `SELECT` statement at runtime.

Note that the primary key for this table consists of *Artist* and *SongTitle*.

The following are some DynamoDB `Query` examples.

```
// Return a single song, by primary key

{
    TableName: "Music",
    KeyConditionExpression: "Artist = :a and SongTitle = :t",
    ExpressionAttributeValues: {
        ":a": "No One You Know",
        ":t": "Call Me Today"
    }
}
```

```
// Return all of the songs by an artist

{
    TableName: "Music",
    KeyConditionExpression: "Artist = :a",
    ExpressionAttributeValues: {
        ":a": "No One You Know"
    }
}
```

```
// Return all of the songs by an artist, matching first part of title

{
    TableName: "Music",
    KeyConditionExpression: "Artist = :a and begins_with(SongTitle, :t)",
    ExpressionAttributeValues: {
        ":a": "No One You Know",
        ":t": "Call"
    }
}
```

```
// Return all of the songs by an artist, only if the price is less than 1.00

{
    TableName: "Music",
    KeyConditionExpression: "Artist = :a",
    FilterExpression: "Price < :p",
    ExpressionAttributeValues: {
        ":a": "No One You Know",
        ":p": 1.00
    }
}
```

**Note**  
A `FilterExpression` is applied after the `Query` reads matching items, so it does not reduce the read capacity consumed. Where possible, model your data so that range conditions use `KeyConditionExpression` on the sort key for efficient queries. For more information, see [Querying tables in DynamoDB](Query.md).

------
#### [ PartiQL for DynamoDB ]

With PartiQL, you can perform a query by using the `ExecuteStatement` operation and the `Select` statement on the partition key.

```
SELECT AlbumTitle, Year, Price
FROM Music
WHERE Artist='No One You Know'
```

Using the `SELECT` statement in this way returns all the songs associated with this particular `Artist`.

For code examples using `Select` and `ExecuteStatement`, see [PartiQL select statements for DynamoDB](ql-reference.select.md).

------

# Differences in scanning a table
<a name="SQLtoNoSQL.ReadData.Scan"></a>

In SQL, a `SELECT` statement without a `WHERE` clause will return every row in a table. In Amazon DynamoDB, the `Scan` operation does the same thing. In both cases, you can retrieve all of the items or just some of them.

Whether you are using a SQL or a NoSQL database, scans should be used sparingly because they can consume large amounts of system resources. Sometimes a scan is appropriate (such as scanning a small table) or unavoidable (such as performing a bulk export of data). However, as a general rule, you should design your applications to avoid performing scans. For more information, see [Querying tables in DynamoDB](Query.md).

**Note**  
Doing a bulk export also creates at least 1 file per partition. All of the items in each file are from that particular partition's hashed keyspace.

**Topics**
+ [

## Scanning a table with SQL
](#SQLtoNoSQL.ReadData.Scan.SQL)
+ [

## Scanning a table in DynamoDB
](#SQLtoNoSQL.ReadData.Scan.DynamoDB)

## Scanning a table with SQL
<a name="SQLtoNoSQL.ReadData.Scan.SQL"></a>

When using SQL you can scan a table and retrieve all of its data by using a `SELECT` statement without specifying a `WHERE` clause. You can request one or more columns in the result. Or you can request all of them if you use the wildcard character (\$1).

The following are examples of using a `SELECT` statement.

```
/* Return all of the data in the table */
SELECT * FROM Music;
```

```
/* Return all of the values for Artist and Title */
SELECT Artist, Title FROM Music;
```

## Scanning a table in DynamoDB
<a name="SQLtoNoSQL.ReadData.Scan.DynamoDB"></a>

In Amazon DynamoDB, you can use either the DynamoDB API or [PartiQL](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html) (a SQL-compatible query language) to perform a scan on a table.

------
#### [ DynamoDB API ]

With the DynamoDB API, you use the `Scan` operation to return one or more items and item attributes by accessing every item in a table or a secondary index.

```
// Return all of the data in the table
{
    TableName:  "Music"
}
```

```
// Return all of the values for Artist and Title
{
    TableName:  "Music",
    ProjectionExpression: "Artist, Title"
}
```

The `Scan` operation also provides a `FilterExpression` parameter, which you can use to discard items that you do not want to appear in the results. A `FilterExpression` is applied after the scan is performed, but before the results are returned to you. (This is not recommended with large tables. You are still charged for the entire `Scan`, even if only a few matching items are returned.)

------
#### [ PartiQL for DynamoDB ]

With PartiQL, you perform a scan by using the `ExecuteStatement` operation to return all the contents for a table using the `Select` statement.

```
SELECT AlbumTitle, Year, Price
FROM Music
```

Note that this statement will return all items for in the Music table. 

For code examples using `Select` and `ExecuteStatement`, see [PartiQL select statements for DynamoDB](ql-reference.select.md).

------