# How to join tables

_This page shows how to combine tables in q and compares each join with its ANSI SQL equivalent, or with pandas where SQL has none._

A join combines the columns or rows of two tables. q provides a keyword for each common kind of join, so you rarely write a join condition. Instead, the shape of the arguments decides how rows match:

- **Keyed joins**, such as [`lj`](../../ref/lj.md) and [`ij`](../../ref/ij.md), match the columns of the first table with the key columns of a [keyed table](data_structures/keyed-tables.md).
- **As-of joins**, such as [`aj`](../../ref/aj.md) and [`wj`](../../ref/wj.md), match on one or more columns for equality and on a time column for the most recent value. They are central to time-series analysis and have no direct equivalent in ANSI SQL.

For the full list of join operators, refer to the [joins reference](../../ref/joins.md).

## Sample tables

The examples use US equity trades and quotes generated by the [datagen module](../../modules/datagen/overview.md). To create the tables in memory:
```q
q)([getInMemoryTables]): use `kx.datagen.capmkts
q)(trade; quote; nbbo; master; exnames): getInMemoryTables[]
```

The examples use these tables:

- `trade`: one row per trade, with the exchange where it executed in `ex`.
- `quote`: one row per bid and ask quote.
- `master`: reference data, keyed on `sym`. Its `ex` column holds the exchange where the stock is listed.
- `exnames`: a dictionary from exchange code to exchange name.
```q
q)3#master
sym | description                          cusip     securityType testFlag ex roundLot issueprice
----| -------------------------------------------------------------------------------------------
AAPL| "Apple Inc."                         037833100 A            0        Q  100      255.44
AGEN| "Agenus Inc."                        00847G804 A            0        Q  100      3.99
AIG | "American International Group, Inc." 026874784 A            0        N  100      79.86
```

To keep the results short, most examples join `t`, the first five trades of the day, and `qt`, the quote table without its size columns:
```q
q)show t:5#select sym, time, price, size, ex from trade
sym  time                 price  size ex
----------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L
PRU  0D09:30:00.153776659 103.02 20   J
TSLA 0D09:30:00.167902241 463.29 27   V
EMA  0D09:30:01.205070767 47.51  12   M
CCL  0D09:30:01.508512258 28.55  29   L
q)qt:select sym, time, bid, ask from quote
```

??? example "Load the same tables into an SQL database or pandas"

    Export the tables from q to CSV files. The commands below remove the `0D` prefix from the timespans, so the `time` column loads into an SQL `TIME` column:
    ```q
    q)`:trade.csv 0: csv 0: update time:2_'string time from trade
    `:trade.csv
    q)`:quote.csv 0: csv 0: update time:2_'string time from select sym, time, bid, ask from quote
    `:quote.csv
    q)`:master.csv 0: csv 0: select sym, description, ex from master
    `:master.csv
    ```

    Create the tables, load the CSV files with your database's import tool, and then create `t`:
    ```sql
    CREATE TABLE trade (sym VARCHAR(16), time TIME(9), price DECIMAL(10,2), size INT,
                        stop BOOLEAN, cond CHAR(1), ex CHAR(1));
    CREATE TABLE quote (sym VARCHAR(16), time TIME(9), bid DECIMAL(10,2), ask DECIMAL(10,2));
    CREATE TABLE master (sym VARCHAR(16) PRIMARY KEY, description VARCHAR(100), ex CHAR(1));

    CREATE TABLE t AS (SELECT sym, time, price, size, ex FROM trade
                       ORDER BY time FETCH FIRST 5 ROWS ONLY) WITH DATA;
    ```

    The SQL results on this page come from SQLite. SQL does not guarantee row order without `ORDER BY`, so your database might return rows in a different order.

    The pandas examples load the same CSV files:
    ```python
    >>> import pandas as pd
    >>> trade = pd.read_csv('trade.csv', usecols=['sym', 'time', 'price', 'size', 'ex'])
    >>> quote = pd.read_csv('quote.csv')
    >>> trade['time'] = pd.to_timedelta(trade['time'])
    >>> quote['time'] = pd.to_timedelta(quote['time'])
    >>> t = trade.head(5)
    ```

## Left join: `lj`

A left join keeps every row of the first table and adds the columns of the second. In q, the second argument of [`lj`](../../ref/lj.md) must be a keyed table, and its key columns are the join columns. To add the company name and listing exchange to each trade, key the columns you need from `master` on `sym` with [`xkey`](../../ref/keys.md#xkey):


**q**
```q
q)t lj `sym xkey select sym, description, listEx:ex from master
sym  time                 price  size ex description                  listEx
----------------------------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  "Cousins Properties Inc."    N
PRU  0D09:30:00.153776659 103.02 20   J  "Prudential Financial, Inc." N
TSLA 0D09:30:00.167902241 463.29 27   V  "Tesla, Inc. Common Stock"   Q
EMA  0D09:30:01.205070767 47.51  12   M  "Emera Incorporated"         N
CCL  0D09:30:01.508512258 28.55  29   L  "Carnival Corporation"       N
```


**SQL**
```sql
SELECT t.*, m.description, m.ex AS listEx
FROM t
LEFT JOIN master m ON t.sym = m.sym;

sym          time         price   size  ex         description          listEx
----  ------------------  ------  ----  --  --------------------------  ------
CUZ   09:30:00.011627745   29.06    65  L   Cousins Properties Inc.     N
PRU   09:30:00.153776659  103.02    20  J   Prudential Financial, Inc.  N
TSLA  09:30:00.167902241  463.29    27  V   Tesla, Inc. Common Stock    Q
EMA   09:30:01.205070767   47.51    12  M   Emera Incorporated          N
CCL   09:30:01.508512258   28.55    29  L   Carnival Corporation        N
```


The result always has the same number of rows as the first table, because each key in a keyed table identifies a single row.

q has no right join. To get one, swap the arguments, keying the original left table first. This works only if that table's join columns are unique.

### Columns that appear in both tables

The example above renames `ex` to `listEx`, because both tables have an `ex` column with different meanings. If a non-key column appears in both tables, `lj` takes its value from the second table for every matching row, even when that value is null. Without the rename, the listing exchange replaces the trade's exchange:


**q**
```q
q)t lj `sym xkey select sym, ex from master
sym  time                 price  size ex
----------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   N
PRU  0D09:30:00.153776659 103.02 20   N
TSLA 0D09:30:00.167902241 463.29 27   Q
EMA  0D09:30:01.205070767 47.51  12   N
CCL  0D09:30:01.508512258 28.55  29   N
```


**SQL**
```sql
SELECT t.sym, t.time, t.price, t.size,
  CASE WHEN m.sym IS NULL THEN t.ex
       ELSE m.ex END AS ex
FROM t
LEFT JOIN master m ON t.sym = m.sym;

sym          time         price   size  ex
----  ------------------  ------  ----  --
CUZ   09:30:00.011627745   29.06    65  N
PRU   09:30:00.153776659  103.02    20  N
TSLA  09:30:00.167902241  463.29    27  Q
EMA   09:30:01.205070767   47.51    12  N
CCL   09:30:01.508512258   28.55    29  N
```


The column also changes type, from symbol in `t` to char in `master`, and q signals no error. This behavior is the same as an [upsert](data_structures/keyed-tables.md#upserting-records), and it is useful when the second table holds corrections. Otherwise, rename the clashing columns in the second table before you join.

Keyed joins also match the join columns by name. If they have different names in the two tables, rename one of them with [`xcol`](../../ref/cols.md#xcol) first.

### Rows with no match

Every stock in `t` appears in `master`. Where a row of the first table has no match, the new columns are null. To show this, the following keyed table assigns a sector to only some of the stocks:


**q**
```q
q)show sector:([sym:`CUZ`TSLA`CCL`AAPL] sector:`RealEstate`ConsumerDisc`ConsumerDisc`Technology)
sym | sector
----| ------------
CUZ | RealEstate
TSLA| ConsumerDisc
CCL | ConsumerDisc
AAPL| Technology
q)t lj sector
sym  time                 price  size ex sector
-----------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  RealEstate
PRU  0D09:30:00.153776659 103.02 20   J
TSLA 0D09:30:00.167902241 463.29 27   V  ConsumerDisc
EMA  0D09:30:01.205070767 47.51  12   M
CCL  0D09:30:01.508512258 28.55  29   L  ConsumerDisc
```


**SQL**
```sql
CREATE TABLE sector (sym VARCHAR(16) PRIMARY KEY, sector VARCHAR(16));
INSERT INTO sector VALUES
  ('CUZ','RealEstate'), ('TSLA','ConsumerDisc'),
  ('CCL','ConsumerDisc'), ('AAPL','Technology');

SELECT t.*, s.sector
FROM t
LEFT JOIN sector s ON t.sym = s.sym;

sym          time         price   size  ex     sector
----  ------------------  ------  ----  --  ------------
CUZ   09:30:00.011627745   29.06    65  L   RealEstate
PRU   09:30:00.153776659  103.02    20  J
TSLA  09:30:00.167902241  463.29    27  V   ConsumerDisc
EMA   09:30:01.205070767   47.51    12  M
CCL   09:30:01.508512258   28.55    29  L   ConsumerDisc
```


### Join on several columns

To match on more than one column, key the second table on all of them. A `select` with a `by` clause returns a table keyed on the `by` columns, so you can join an aggregation directly. The following adds the total volume each stock traded on each exchange during the day:


**q**
```q
q)t lj select exVol:sum size by sym, ex from trade
sym  time                 price  size ex exVol
----------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  467
PRU  0D09:30:00.153776659 103.02 20   J  560
TSLA 0D09:30:00.167902241 463.29 27   V  978
EMA  0D09:30:01.205070767 47.51  12   M  341
CCL  0D09:30:01.508512258 28.55  29   L  233
```


**SQL**
```sql
SELECT t.*, v.exVol
FROM t
LEFT JOIN (SELECT sym, ex, SUM(size) AS exVol
           FROM trade GROUP BY sym, ex) v
  ON t.sym = v.sym AND t.ex = v.ex;

sym          time         price   size  ex  exVol
----  ------------------  ------  ----  --  -----
CUZ   09:30:00.011627745   29.06    65  L     467
PRU   09:30:00.153776659  103.02    20  J     560
TSLA  09:30:00.167902241  463.29    27  V     978
EMA   09:30:01.205070767   47.51    12  M     341
CCL   09:30:01.508512258   28.55    29  L     233
```


## Inner join: `ij`

An inner join returns only the rows of the first table that have a match in the second. Like `lj`, [`ij`](../../ref/ij.md) takes a keyed table as its second argument:


**q**
```q
q)t ij sector
sym  time                 price  size ex sector
-----------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  RealEstate
TSLA 0D09:30:00.167902241 463.29 27   V  ConsumerDisc
CCL  0D09:30:01.508512258 28.55  29   L  ConsumerDisc
```


**SQL**
```sql
SELECT t.*, s.sector
FROM t
INNER JOIN sector s ON t.sym = s.sym;

sym          time         price   size  ex     sector
----  ------------------  ------  ----  --  ------------
CUZ   09:30:00.011627745   29.06    65  L   RealEstate
TSLA  09:30:00.167902241  463.29    27  V   ConsumerDisc
CCL   09:30:01.508512258   28.55    29  L   ConsumerDisc
```


The `PRU` and `EMA` trades are dropped because `sector` has no row for them.

## Equi join: `ej`

An SQL inner join returns one row for every matching pair, so a row of the first table can appear many times. [`ej`](../../ref/ej.md) does the same. It takes the join columns as its first argument, and the second table does not need to be keyed. The following table lists the benchmark indexes that some of the stocks belong to. `TSLA` belongs to two:


**q**
```q
q)show idx:([] sym:`TSLA`TSLA`PRU`CCL; bench:`SPX`NDX`SPX`SPX)
sym  bench
----------
TSLA SPX
TSLA NDX
PRU  SPX
CCL  SPX
q)ej[`sym;t;idx]
sym  time                 price  size ex bench
----------------------------------------------
PRU  0D09:30:00.153776659 103.02 20   J  SPX
TSLA 0D09:30:00.167902241 463.29 27   V  SPX
TSLA 0D09:30:00.167902241 463.29 27   V  NDX
CCL  0D09:30:01.508512258 28.55  29   L  SPX
```


**SQL**
```sql
CREATE TABLE idx (sym VARCHAR(16), bench VARCHAR(8));
INSERT INTO idx VALUES
  ('TSLA','SPX'), ('TSLA','NDX'), ('PRU','SPX'), ('CCL','SPX');

SELECT t.*, i.bench
FROM t
INNER JOIN idx i ON t.sym = i.sym;

sym          time         price   size  ex  bench
----  ------------------  ------  ----  --  -----
TSLA  09:30:00.167902241  463.29    27  V   SPX
TSLA  09:30:00.167902241  463.29    27  V   NDX
PRU   09:30:00.153776659  103.02    20  J   SPX
CCL   09:30:01.508512258   28.55    29  L   SPX
```


Compare this with `ij`. If you key `idx` on `sym`, which has duplicates, `ij` uses only the first matching row for each key:
```q
q)t ij `sym xkey idx
sym  time                 price  size ex bench
----------------------------------------------
PRU  0D09:30:00.153776659 103.02 20   J  SPX
TSLA 0D09:30:00.167902241 463.29 27   V  SPX
CCL  0D09:30:01.508512258 28.55  29   L  SPX
```

Use `ej` when the second table can have several matches per row and you want all of them. Use `ij` or `lj` when each row has at most one match.

## Union join: `uj`

[`uj`](../../ref/uj.md) combines all the rows and all the columns of two tables. Its behavior depends on whether the arguments are keyed. Both arguments must be keyed, or neither.

### Keyed tables: full outer join

With two keyed tables, `uj` returns one row for each key in either table. This is a full outer join on the key columns. The following compares the volume each stock traded in the first and last two seconds of the day. Some stocks traded in only one of them:


**q**
```q
q)openVol:select openVol:sum size by sym from trade where time<09:30:02
q)closeVol:select closeVol:sum size by sym from trade where time>=15:59:58
q)openVol uj closeVol
sym   | openVol closeVol
------| ----------------
CCL   | 29      79
CUZ   | 65      20
EMA   | 12
ITOL  | 67
PBW   | 82
PRU   | 20
TSLA  | 27
ARBE.W|         32
BSBK  |         43
DERM  |         12
IBM   |         72
ISHG  |         90
META  |         17
PEP   |         97
VDE   |         17
```


**SQL**
```sql
SELECT COALESCE(o.sym, c.sym) AS sym,
       o.openVol, c.closeVol
FROM (SELECT sym, SUM(size) AS openVol FROM trade
      WHERE time < '09:30:02' GROUP BY sym) o
FULL OUTER JOIN
     (SELECT sym, SUM(size) AS closeVol FROM trade
      WHERE time >= '15:59:58' GROUP BY sym) c
  ON o.sym = c.sym;

 sym    openVol  closeVol
------  -------  --------
CCL          29        79
CUZ          65        20
EMA          12
ITOL         67
PBW          82
PRU          20
TSLA         27
ARBE.W                 32
BSBK                   43
DERM                   12
IBM                    72
ISHG                   90
META                   17
PEP                    97
VDE                    17
```


As with `lj`, values from the second table replace values from the first table in columns that appear in both.

### Unkeyed tables: append rows

To append the rows of a table with the same columns, use the [join](../../ref/join.md) operator `,`. This is the equivalent of `UNION ALL`. The following appends the last two trades of the day to `t`:


**q**
```q
q)last2:-2#select sym, time, price, size, ex from trade
q)t,last2
sym  time                 price  size ex
----------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L
PRU  0D09:30:00.153776659 103.02 20   J
TSLA 0D09:30:00.167902241 463.29 27   V
EMA  0D09:30:01.205070767 47.51  12   M
CCL  0D09:30:01.508512258 28.55  29   L
VDE  0D15:59:59.899706248 133.57 17   Y
ISHG 0D15:59:59.911661552 80.48  90   Y
```


**SQL**
```sql
SELECT * FROM t
UNION ALL
SELECT * FROM last2;

sym          time         price   size  ex
----  ------------------  ------  ----  --
CUZ   09:30:00.011627745   29.06    65  L
PRU   09:30:00.153776659  103.02    20  J
TSLA  09:30:00.167902241  463.29    27  V
EMA   09:30:01.205070767   47.51    12  M
CCL   09:30:01.508512258   28.55    29  L
VDE   15:59:59.899706248  133.57    17  Y
ISHG  15:59:59.911661552   80.48    90  Y
```


If the tables have different columns, such as trades and quotes, `,` signals a `'mismatch` error. Use `uj` instead. It matches columns by name and fills missing values with nulls, whereas in SQL you must list the columns and add the missing ones yourself:


**q**
```q
q)q2:2#qt
q)t,q2
'mismatch
q)t uj q2
sym  time                 price  size ex bid    ask
------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L
PRU  0D09:30:00.153776659 103.02 20   J
TSLA 0D09:30:00.167902241 463.29 27   V
EMA  0D09:30:01.205070767 47.51  12   M
CCL  0D09:30:01.508512258 28.55  29   L
ORCL 0D09:30:00.003406498                289.88 290.04
TZOO 0D09:30:00.004116350                9.87   9.89
```


**SQL**
```sql
SELECT sym, time, price, size, ex,
       NULL AS bid, NULL AS ask
FROM t
UNION ALL
SELECT sym, time, NULL, NULL, NULL, bid, ask
FROM q2;

sym          time         price   size  ex   bid     ask
----  ------------------  ------  ----  --  ------  ------
CUZ   09:30:00.011627745   29.06    65  L
PRU   09:30:00.153776659  103.02    20  J
TSLA  09:30:00.167902241  463.29    27  V
EMA   09:30:01.205070767   47.51    12  M
CCL   09:30:01.508512258   28.55    29  L
ORCL  09:30:00.003406498                    289.88  290.04
TZOO  09:30:00.004116350                      9.87    9.89
```


> **Note — `uj` does not check column types**
>
>
> `uj` on unkeyed tables appends rows without checking that matching columns have the same type. If the types differ, the result has a mixed list column. Use [`upsert`](../../ref/upsert.md) or `,` when you want q to signal an error instead.
>
## Plus join: `pj`

[`pj`](../../ref/pj.md) is a left join that adds values instead of replacing them. For each matching row, it adds the numeric columns of the second table to the same columns of the first. A null in the second table counts as zero. The following adds the afternoon volume of each stock to its morning volume:


**q**
```q
q)am:select sum size by sym from trade where time<12:00
q)pm:select sum size by sym from trade where time>=12:00
q)3#am pj pm
sym | size
----| ----
AAPL| 2788
AGEN| 3357
AIG | 3078
```


**SQL**
```sql
SELECT a.sym,
       a.size + COALESCE(p.size, 0) AS size
FROM (SELECT sym, SUM(size) AS size FROM trade
      WHERE time < '12:00' GROUP BY sym) a
LEFT JOIN
     (SELECT sym, SUM(size) AS size FROM trade
      WHERE time >= '12:00' GROUP BY sym) p
  ON a.sym = p.sym
ORDER BY a.sym
FETCH FIRST 3 ROWS ONLY;

sym   size
----  ----
AAPL  2788
AGEN  3357
AIG   3078
```


The result is the volume for the whole day:
```q
q)(am pj pm)~select sum size by sym from trade
1b
```

As with any left join, `pj` drops the rows of the second table that have no match in the first.

## Join row by row: `,'`

To join two tables of the same length row by row, use [join](../../ref/join.md) with the [Each](../../ref/maps.md#each) iterator: `,'`. Row _i_ of the result combines row _i_ of each table, and no columns are matched. SQL tables have no row order, so there is no SQL equivalent. In pandas, use `concat` along the column axis:


**q**
```q
q)q5:5#qt
q)q5,'select mid:0.5*bid+ask, spread:ask-bid from q5
sym  time                 bid    ask    mid    spread
-----------------------------------------------------
ORCL 0D09:30:00.003406498 289.88 290.04 289.96 0.16
TZOO 0D09:30:00.004116350 9.87   9.89   9.88   0.02
VWID 0D09:30:00.004184972 33.85  33.87  33.86  0.02
CUZ  0D09:30:00.011627744 29.05  29.07  29.06  0.02
PBW  0D09:30:00.108156062 30.51  30.53  30.52  0.02
```


**pandas**
```python
>>> q5 = quote.head(5)
>>> pd.concat([q5, pd.DataFrame({'mid': 0.5 * (q5.bid + q5.ask), 'spread': q5.ask - q5.bid})], axis=1)
    sym                      time     bid     ask     mid  spread
0  ORCL 0 days 09:30:00.003406498  289.88  290.04  289.96    0.16
1  TZOO 0 days 09:30:00.004116350    9.87    9.89    9.88    0.02
2  VWID 0 days 09:30:00.004184972   33.85   33.87   33.86    0.02
3   CUZ 0 days 09:30:00.011627744   29.05   29.07   29.06    0.02
4   PBW 0 days 09:30:00.108156062   30.51   30.53   30.52    0.02
```


If both tables have a column with the same name, the result takes it from the second table.

## Look up values in a dictionary

A dictionary maps keys to values, so indexing it with a column is a lookup join. `exnames` maps each exchange code to its name:
```q
q)update exname:exnames ex from t
sym  time                 price  size ex exname
-------------------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  "Long-Term Stock Exchange"
PRU  0D09:30:00.153776659 103.02 20   J  "Cboe EDGA Exchange"
TSLA 0D09:30:00.167902241 463.29 27   V  "The Investors' Exchange"
EMA  0D09:30:01.205070767 47.51  12   M  "Chicago Stock Exchange"
CCL  0D09:30:01.508512258 28.55  29   L  "Long-Term Stock Exchange"
```

In SQL, you would store the mapping as a table and use a left join.

## Implicit join with a foreign key

If a column is a [foreign key](../interact_with_databases/relationships/foreign-keys.md) to a keyed table, you can reach the columns of that table with dot notation in a query. q performs the join for you. The following makes `sym` a foreign key to `master` and reads the company name and listing exchange through it:


**q**
```q
q)f:update sym:`master$sym from t
q)select sym, time, price, sym.description, sym.ex from f
sym  time                 price  description                  ex
----------------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  "Cousins Properties Inc."    N
PRU  0D09:30:00.153776659 103.02 "Prudential Financial, Inc." N
TSLA 0D09:30:00.167902241 463.29 "Tesla, Inc. Common Stock"   Q
EMA  0D09:30:01.205070767 47.51  "Emera Incorporated"         N
CCL  0D09:30:01.508512258 28.55  "Carnival Corporation"       N
```


**SQL**
```sql
SELECT t.sym, t.time, t.price,
       m.description, m.ex
FROM t
JOIN master m ON t.sym = m.sym;

sym          time         price          description          ex
----  ------------------  ------  --------------------------  --
CUZ   09:30:00.011627745   29.06  Cousins Properties Inc.     N
PRU   09:30:00.153776659  103.02  Prudential Financial, Inc.  N
TSLA  09:30:00.167902241  463.29  Tesla, Inc. Common Stock    Q
EMA   09:30:01.205070767   47.51  Emera Incorporated          N
CCL   09:30:01.508512258   28.55  Carnival Corporation        N
```


As in SQL, the foreign key enforces referential integrity. A value that is not a key of `master` signals an error:
```q
q)`master$`XYZ
'cast
```

The foreign key is resolved once, when you create the column, so an implicit join is faster than an explicit `lj` and builds no intermediate table.

## As-of join: `aj`

An as-of join matches each row of the first table with the most recent row of the second table at or before its time. The typical use is finding the quote that prevailed at the time of each trade.

ANSI SQL has no as-of join. Some databases add one as an extension, but in standard SQL you need a correlated subquery or a window function, which is slow on large tables. pandas provides `merge_asof`.

These are the quotes for the stocks in `t` up to the time of the last trade in `t`:
```q
q)select from qt where sym in t`sym, time<09:30:01.6
sym  time                 bid    ask
---------------------------------------
CUZ  0D09:30:00.011627744 29.05  29.07
PRU  0D09:30:00.153776659 103.01 103.05
CCL  0D09:30:00.155456672 28.55  28.57
TSLA 0D09:30:00.167902243 463.22 463.43
TSLA 0D09:30:00.275206284 463.22 463.4
EMA  0D09:30:00.386418549 47.5   47.53
TSLA 0D09:30:00.653276833 463.29 463.47
EMA  0D09:30:01.205070767 47.5   47.52
CCL  0D09:30:01.304340681 28.55  28.57
CCL  0D09:30:01.508512259 28.54  28.56
```

The first argument of [`aj`](../../ref/aj.md) lists the join columns. All but the last must match exactly, and the last is the column searched as of, typically time:


**q**
```q
q)aj[`sym`time;t;qt]
sym  time                 price  size ex bid    ask
------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  29.05  29.07
PRU  0D09:30:00.153776659 103.02 20   J  103.01 103.05
TSLA 0D09:30:00.167902241 463.29 27   V
EMA  0D09:30:01.205070767 47.51  12   M  47.5   47.52
CCL  0D09:30:01.508512258 28.55  29   L  28.55  28.57
```


**pandas**
```python
>>> pd.merge_asof(t, quote, on='time', by='sym')
    sym                      time   price  size ex     bid     ask
0   CUZ 0 days 09:30:00.011627745   29.06    65  L   29.05   29.07
1   PRU 0 days 09:30:00.153776659  103.02    20  J  103.01  103.05
2  TSLA 0 days 09:30:00.167902241  463.29    27  V     NaN     NaN
3   EMA 0 days 09:30:01.205070767   47.51    12  M   47.50   47.52
4   CCL 0 days 09:30:01.508512258   28.55    29  L   28.55   28.57
```


The `CCL` trade at `09:30:01.508512258` gets the quote from `09:30:01.304340681`, not the one that arrived a nanosecond after the trade. The first `TSLA` quote arrived two nanoseconds after the `TSLA` trade, so the trade has no earlier quote and its quote columns are null.

The result keeps the time of each trade. To see the time of the matching quote instead, use `aj0`:
```q
q)aj0[`sym`time;t;qt]
sym  time                 price  size ex bid    ask
------------------------------------------------------
CUZ  0D09:30:00.011627744 29.06  65   L  29.05  29.07
PRU  0D09:30:00.153776659 103.02 20   J  103.01 103.05
TSLA 0D09:30:00.167902241 463.29 27   V
EMA  0D09:30:01.205070767 47.51  12   M  47.5   47.52
CCL  0D09:30:01.304340681 28.55  29   L  28.55  28.57
```

> **Warning — Sort the second table and list the time column last**
>
>
> `aj` returns the last matching row of the second table in row order. The second table must be sorted by time within each `sym`, otherwise the result is wrong but q signals no error. The same applies if you put the columns in the wrong order, such as `` `time`sym``.
>
For large tables, apply the `g#` attribute to `sym` on an in-memory table, or use the `p#` attribute on disk. The datagen tables already have `g#` on `sym`. The [`aj` reference](../../ref/aj.md#performance) explains the options.

## Window join: `wj`

A window join aggregates the rows of the second table that fall within a time window around each row of the first. Use it, for example, to find the best bid and ask within one second either side of each trade.

The first argument of [`wj`](../../ref/wj.md) is a pair of lists: the start and end time of each window, one per row of the first table:
```q
q)show w:(-1 1*0D00:00:01)+\:t`time
0D09:29:59.011627745 0D09:29:59.153776659 0D09:29:59.167902241 0D09:30:00.205070767 0D09:30:00.508512258
0D09:30:01.011627745 0D09:30:01.153776659 0D09:30:01.167902241 0D09:30:02.205070767 0D09:30:02.508512258
```

The last argument lists the second table, followed by an aggregation function and a column for each new column. The second table must be sorted by `sym` then `time`, with the `p#` attribute on `sym`.

The [`wj1`](../../ref/wj.md) form uses only the quotes inside each window. Standard SQL can express this with a range condition and `GROUP BY`:


**q**
```q
q)qp:update `p#sym from `sym`time xasc qt
q)wj1[w;`sym`time;t;(qp;(max;`bid);(min;`ask))]
sym  time                 price  size ex bid    ask
------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  29.05  29.07
PRU  0D09:30:00.153776659 103.02 20   J  103.01 103.05
TSLA 0D09:30:00.167902241 463.29 27   V  463.29 463.4
EMA  0D09:30:01.205070767 47.51  12   M  47.5   47.52
CCL  0D09:30:01.508512258 28.55  29   L  28.55  28.56
```


**SQL**
```sql
SELECT t.sym, t.time, t.price, t.size, t.ex,
       MAX(q.bid) AS bid, MIN(q.ask) AS ask
FROM t
LEFT JOIN quote q
  ON q.sym = t.sym
 AND q.time BETWEEN t.time - INTERVAL '1' SECOND
                AND t.time + INTERVAL '1' SECOND
GROUP BY t.sym, t.time, t.price, t.size, t.ex
ORDER BY t.time;

sym          time         price   size  ex   bid     ask
----  ------------------  ------  ----  --  ------  ------
CUZ   09:30:00.011627745   29.06    65  L    29.05   29.07
PRU   09:30:00.153776659  103.02    20  J   103.01  103.05
TSLA  09:30:00.167902241  463.29    27  V   463.29   463.4
EMA   09:30:01.205070767   47.51    12  M     47.5   47.52
CCL   09:30:01.508512258   28.55    29  L    28.55   28.56
```


Unlike `aj`, the window extends past the trade, so the `TSLA` trade now picks up the quotes that arrived after it.

Where a window holds no quotes, q applies the aggregation to an empty list, so `max` returns negative infinity (`-0w`) and `min` returns infinity (`0w`). SQL returns null.

`wj` also includes the quote that prevails at the start of each window, because a quote stays valid until the next one arrives. This has no simple SQL equivalent. To see which values each form uses, pass the identity function `::` in place of the aggregation:
```q
q)wj[w;`sym`time;t;(qp;(::;`bid))]
sym  time                 price  size ex bid
-------------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  ,29.05
PRU  0D09:30:00.153776659 103.02 20   J  ,103.01
TSLA 0D09:30:00.167902241 463.29 27   V  463.22 463.22 463.29
EMA  0D09:30:01.205070767 47.51  12   M  47.5 47.5
CCL  0D09:30:01.508512258 28.55  29   L  28.55 28.55 28.54
q)wj1[w;`sym`time;t;(qp;(::;`bid))]
sym  time                 price  size ex bid
-------------------------------------------------------------
CUZ  0D09:30:00.011627745 29.06  65   L  ,29.05
PRU  0D09:30:00.153776659 103.02 20   J  ,103.01
TSLA 0D09:30:00.167902241 463.29 27   V  463.22 463.22 463.29
EMA  0D09:30:01.205070767 47.51  12   M  47.5 47.5
CCL  0D09:30:01.508512258 28.55  29   L  28.55 28.54
```

The `CCL` window starts at `09:30:00.508512258`. The quote from `09:30:00.155456672` is still in force at that time, so `wj` includes its bid of `28.55`, and `wj1` does not.

## Choose a join

| To get | q | ANSI SQL | pandas |
| --- | --- | --- | --- |
| Every row of `x`, with matching columns of `y` | `x lj y` | `LEFT JOIN` | `merge(how='left')` |
| Rows of `x` with a match in `y` | `x ij y` | `INNER JOIN` on a key | `merge(how='inner')` |
| One row per matching pair | `ej[c;x;y]` | `INNER JOIN` | `merge(how='inner')` |
| All keys of both keyed tables | `x uj y` | `FULL OUTER JOIN` | `merge(how='outer')` |
| Rows of both tables, same columns | `x,y` | `UNION ALL` | `concat` |
| Rows of both tables, any columns | `x uj y` | `UNION ALL` with `NULL` columns | `concat` |
| Values of `y` added to `x` | `x pj y` | `LEFT JOIN` with `x.v + COALESCE(y.v, 0)` | `merge(how='left')`, then add |
| Row _i_ of `x` with row _i_ of `y` | `x,'y` | none | `concat(axis=1)` |
| Most recent row of `y` for each row of `x` | `aj[c;x;y]` | none | `merge_asof` |
| Aggregates of `y` over a time window | `wj[w;c;x;y]` | Range join with `GROUP BY` (`wj1` only) | none |

In the keyed joins `lj`, `ij`, `uj` and `pj`, `y` is a keyed table and its key columns are the join columns.

## Next steps

- Learn more about [keyed tables](data_structures/keyed-tables.md), the second argument of most keyed joins.
- Link tables permanently with [foreign keys](../interact_with_databases/relationships/foreign-keys.md) and [linking columns](../interact_with_databases/relationships/linking-columns.md).
- Read the [joins reference](../../ref/joins.md) for the full list of join operators, including `asof`, `ajf` and `ijf`.
- Query joined tables with [qSQL](../querying/select.md).
- Generate larger test data with the [datagen module](../../modules/datagen/overview.md).