# 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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.lenses.io/latest/devx/6.0/user-guide/sql-studio/views-and-synonyms.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.