Joins
What are Joins?
Joins are operations which combine columns from one or more tables into a new table. For example, Let's say we have a table containing employees and their departments, and a table containing the department names, buildings and managers:
At some point a user may want to find out an employee's Manager given the employee's Name or Employee ID. As you can see, to do this, they would first need to find the employee's Department in the first table, and then use that value in the second table to find the Manager.
One way to achieve this would be to join both tables into one big table, which would essentially be the table on the left but with two extra columns (for Building and Manager) added where there is matching Department. However for our example we only need Name, Department and Manager, so we could just return those columns.
Either way we have just joined the two tables using the 'Department' column - where we found an exact match between the 'Department' column in each table, we joined the two tables together,
Ever since there have been relational databases, there have been joins. In the database world, the most well known joins by far are those used in the SQL language. These are: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. When most people talk about joins they are usually referring to these. See this blog post for a visual explanation of SQL joins.
Some of the joins used in kdb+ are based on these joins, with similar behaviour.
Joins in kdb+
In kdb+, a join combines data from two tables, or from a table and a dictionary. Joins are essential to most implementations of kdb+ databases; consider a basic Transaction Cost Analysis (TCA) scenario in which the prevailing price in the market at the time of a particular trade is compared to the price of the trade itself - we need to join the table of quotes with the table of trades to do so.
On code.kx, kdb+ joins are split between keyed joins and as-of joins. Let's stick with that division when we look at them in more detail below: