# Views & synonyms

Lenses supports the typical SQL commands supported by a relational database:

* `CREATE`
* `DROP`
* `TRUNCATE`
* `DELETE`
* `SHOW VIEWS`

A `view` is a virtual table, generated dynamically based on the results of a `SELECT` statement.

A view looks and acts just like a real table, but is always created on the fly as required, so it is always up to date.

A `synonym` is an alias for a table. This is useful if you have a topic with a long, unwieldy name like customer\_reports\_emea\_april\_2018\_to\_march\_2018 and you want to access this as customer\_reports.

## CREATE VIEW <a href="#creating-a-view" id="creating-a-view"></a>

To create a view:

```sql
CREATE VIEW <viewname> AS <query>
```

Where `viewname` is the name of the virtual table that is used to access the records in the view, and the query is a standard `SELECT` statement.

Then we can query the view:

```sql
SELECT *
FROM customer_emails
```

A view acts as a virtual table. This means that a view can be filtered even more or that a projection can be applied to a view:

```sql
SELECT *
FROM customer_emails
WHERE name LIKE 'sam%'
```

## DROP VIEW <a href="#deleting-a-view" id="deleting-a-view"></a>

To delete a view:

```sql
DROP VIEW <viewname>
```

If you wish to modify an existing view, use the syntax above to delete it, and then create a new view with the same name.

## SHOW VIEW <a href="#inspecting-a-view" id="inspecting-a-view"></a>

To see a definition of a view. You can use the following syntax:

```sql
SHOW VIEW <viewname>
```

## CREATE SYNONYM <a href="#creating-a-synonym" id="creating-a-synonym"></a>

To create a synonym:

```sql
CREATE SYNONYM <name> FOR <table>
```

## DROP SYNONYM <a href="#deleting-a-synonym" id="deleting-a-synonym"></a>

To delete a synonym:

```sql
DROP SYNONYM <name>
```

If you wish to modify an existing synonym, use the syntax above to delete it, and then create a new synonym with the same name.

## Common use cases <a href="#common-use-cases" id="common-use-cases"></a>

Three common reasons for using a view are:

* creating a projection from a table with a large number of fields
* representing joins as a single table
* and creating a preset filter

We will cover each scenario with an example.

### Projection subset <a href="#projection-subset" id="projection-subset"></a>

If we have a table called customers which contains full customer records - name, email, age, registration date, country, password, and many others – and we find ourselves repeatedly querying it for just name and email.

A view could be created that returns just the name and email as a projection.

```sql
CREATE VIEW customer_emails AS
SELECT 
    name
    , email
FROM customers
```

There is no reason to specify the projection each time.

The benefit is more significant when we want to select a higher number of fields - say a topic with 50 fields, and we want to select only 15.

### Representing joins <a href="#representing-joins" id="representing-joins"></a>

The statement that is used to generate the view can consist of one or more tables. One use case of views is to represent joined tables as if they were a single table. This avoids the need for writing a complex join query each time.

```sql
CREATE VIEW orders AS
SELECT 
    c.customer_id
    , c.customer_name
    , c.customer_email
    , o.order_id
    , o.order_date
    , a.address
    , a.postcode
FROM customers c JOIN orders o ON c.customer_id = o.customer_id
    JOIN addresses a ON o.delivery_address_id = a.address_id
```

Then we can select from this join like this:

```sql
SELECT *
FROM orders
```

### Preset filter <a href="#preset-filter" id="preset-filter"></a>

Finally, another use case is to define a filter that is commonly used. If a topic contains transactions, and we often found ourselves searching for transactions from the UK. We could run this query each time:

```sql
SELECT *
FROM transactions
WHERE country = "UK"
```

Alternatively, we can set up a view with this filter pre-applied:

```sql
CREATE VIEW transactions_uk AS
SELECT *
FROM transactions
WHERE country = "UK"
```

Then use a `SELECT` query:

```sql
SELECT *
FROM transactions_uk
```