# Joining topics

### Joins <a href="#joins" id="joins"></a>

Lenses allows you to combine records from two tables. A query can contain zero, one or multiple JOIN operations.

Create an `orders` table and insert some data into it:

<a class="button secondary">Copy</a>

```sql
CREATE TABLE orders(
    _key INT
    , orderDate STRING
    , customerId STRING
    , amount DOUBLE
) 
FORMAT(int, avro);

INSERT INTO orders (
    _key
    , orderDate
    , customerId
    , amount
)
VALUES
(1, '2018-10-01', '1', 200.50),
(2, '2018-10-11', '1', 813.00),
(3, '2018-10-11', '3', 625.20),
(4, '2018-10-11', '14', 730.00),
(5, '2018-10-11', '10', 440.00),
(6, '2018-10-11', '9', 444.80);
```

With these tables in place, join them to get more information about an order by combining it with the customer information found in the `customer` table:sq

```sql
SELECT 
    o._key AS orderNumber
    , o.amount AS totalAmount
    , c.firstName
    , c.lastName
    , c.city
    , c.country
FROM orders o INNER JOIN customer c
    ON o.customerId = c._key

/*
city        orderNumber     country     totalAmount     lastName    firstName
New York        1               USA         200.5       Smith       Craig
New York        2               USA         813         Smith       Craig
Leeds           3               UK          625.2       Anthony     William
Rio De Janeiro  6               Brazil      444.8       de Ellis    Marquis
Houston         5               USA         440         Milton      Joseph
London          4               UK          730         Wilde       C. J.
*/
```

### Lateral Joins <a href="#lateral-joins" id="lateral-joins"></a>

With lateral joins, Lenses allows you to combine records from a table with the elements of an array expression.

We are going to see in more detail what lateral joins are with an example.

Create a `batched_readings` table and insert some data into it:

```sql
CREATE TABLE batched_readings(
  meter_id int
  , readings int[]
)
FORMAT(int, AVRO);

INSERT INTO batched_readings(
    meter_id
    , readings
) VALUES
(1, [100, 80, 95, 91]),
(2, [87, 93, 100]),
(1, [88, 89, 92, 94]),
(2, [81])
```

You now can use a LATERAL join to inspect, extract and filter the single elements of the `readings` array, as if they were a normal field:

```sql
SELECT
    meter_id
    , reading
 FROM
    batched_readings
    LATERAL readings AS reading
WHERE 
    reading > 90
```

Running that query returns:

| meter\_id | reading |
| --------- | ------- |
| 1         | 100     |
| 1         | 95      |
| 1         | 91      |
| 2         | 93      |
| 1         | 92      |
| 1         | 94      |

You can use multiple `LATERAL` joins, one inside the other, if you want to extract elements from a nested array:

```sql
CREATE TABLE batched_readings_nested(
  sensor_id int
  , nested_readings int[][]
)
FORMAT(int, AVRO);

INSERT INTO batched_readings_nested(
    sensor_id
    , nested_readings
) VALUES
(1, [[100, 101], [103]]),
(2, [[80, 81], [82, 83, 82]]),
(1, [[100], [103, 102], [104]])
```

Running the following query we will obtain the same records of the previous example:

```
SELECT
    meter_id
    , reading
 FROM
    batched_readings
    LATERAL nested_readings AS readings
    LATERAL readings as reading
WHERE 
    reading > 90
```