About Table Joins in Manticore Search

Table joins in Manticore Search enable you to combine documents from two tables by matching related columns. This functionality allows for more complex queries and enhanced data retrieval across multiple tables.

Manticore Search supports two types of joins:

• INNER JOIN: Returns only the rows where there is a match in both tables
• LEFT JOIN: Returns all rows from the left table and the matched rows from the right table

Creating Tables for Joins

In this course, we will use two tables: customers and orders. The customers table contains customer information, while the orders table contains order details linked to customers via customer_id.

First, let's connect to Manticore using the MySQL client:

mysql -P9306 -h0

Now, let's create our tables:

DROP TABLE IF EXISTS customers;

DROP TABLE IF EXISTS orders;

CREATE TABLE customers (id BIGINT, name TEXT, email TEXT, address TEXT) type='rt' min_infix_len='3';

CREATE TABLE orders (id BIGINT, customer_id BIGINT, product TEXT, quantity INT, order_date TEXT) type='rt' min_infix_len='3';

Inserting Sample Data

Let's insert some customers:

INSERT INTO customers (id, name, email, address) VALUES (1, 'Alice Johnson', 'alice@example.com', '123 Maple St');

INSERT INTO customers (id, name, email, address) VALUES (2, 'Bob Smith', 'bob@example.com', '456 Oak St');

INSERT INTO customers (id, name, email, address) VALUES (3, 'Carol White', 'carol@example.com', '789 Pine St');

INSERT INTO customers (id, name, email, address) VALUES (4, 'John Smith', 'john@example.com', '15 Barclays St');

Now let's insert some orders:

INSERT INTO orders (id, customer_id, product, quantity, order_date) VALUES (1, 1, 'Laptop', 1, '2023-01-01');

INSERT INTO orders (id, customer_id, product, quantity, order_date) VALUES (2, 2, 'Phone', 2, '2023-01-02');

INSERT INTO orders (id, customer_id, product, quantity, order_date) VALUES (3, 1, 'Tablet', 1, '2023-01-03');

INSERT INTO orders (id, customer_id, product, quantity, order_date) VALUES (4, 3, 'Monitor', 1, '2023-01-04');

Note that customer with id=4 (John Smith) has no orders.

Using INNER JOIN

INNER JOIN returns only the rows where there is a match in both tables. Let's see it in action.

The basic syntax is:

SELECT select_expr [, select_expr] ...
FROM table1
INNER JOIN table2
ON table1.field = table2.field

Let's retrieve all orders with their corresponding customer information:

SELECT product, customers.name, customers.email FROM orders INNER JOIN customers ON customers.id = orders.customer_id;

This query will return 4 rows - one for each order, with customer details included.

Notice that John Smith (id=4) does not appear in the results because he has no orders. INNER JOIN only includes rows where there's a match in both tables.

Let's try a more specific query with filtering. We'll find all orders by customers whose address contains "Maple":

SELECT product, customers.email, customers.name, customers.address FROM orders INNER JOIN customers ON customers.id = orders.customer_id WHERE MATCH('maple', customers);

This returns only Alice Johnson's orders (Laptop and Tablet) because her address is "123 Maple St".

You can also sort the results:

SELECT product, customers.email, customers.name FROM orders INNER JOIN customers ON customers.id = orders.customer_id ORDER BY customers.email ASC;

This will sort all orders by customer email in ascending order.

Using LEFT JOIN

LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for the right table's columns.

The syntax is:

SELECT select_expr [, select_expr] ...
FROM table1
LEFT JOIN table2
ON table1.field = table2.field

Let's retrieve all customers along with their orders:

SELECT name, orders.product, orders.quantity FROM customers LEFT JOIN orders ON orders.customer_id = customers.id ORDER BY email ASC;

Notice the result: John Smith appears in the results with NULL for orders.product and orders.quantity because he has no orders. This is the key difference between LEFT JOIN and INNER JOIN.

You can filter out customers without orders using IS NOT NULL:

SELECT name, orders.product FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.product IS NOT NULL;

Or find customers who have NOT placed any orders:

SELECT name, email FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.product IS NULL;

This will return only John Smith, since he's the only customer without orders.

You can also count how many orders each customer has. Note that with LEFT JOIN, customers without orders will still appear with a count of 1 (the joined NULL row):

SELECT name, COUNT(*) AS total_rows FROM customers LEFT JOIN orders ON orders.customer_id = customers.id GROUP BY id, name ORDER BY total_rows DESC;

This shows that Alice has 2 rows (2 orders), while Bob, Carol, and John each have 1 row. To count only actual orders, you would need to filter NULL values or use conditional counting.

Full-Text Search in JOINs

One of the powerful features of table joins in Manticore Search is the ability to perform full-text searches on both the left and right tables simultaneously.

You can use separate MATCH() functions for each table in your JOIN query. The query filters results based on text content in both tables.

Let's search for customers whose name contains "smith" and their orders containing "phone":

SELECT name, orders.product FROM customers INNER JOIN orders ON orders.customer_id = customers.id WHERE MATCH('smith', customers) AND MATCH('phone', orders);

This returns only Bob Smith's Phone order, because:

• Bob's name contains "smith"
• His order contains "phone"
• John Smith has no orders, so he doesn't appear (INNER JOIN)

You can also use OR conditions in full-text search:

SELECT name, orders.product FROM customers INNER JOIN orders ON orders.customer_id = customers.id WHERE MATCH('alice | bob', customers);

This returns all orders from either Alice or Bob.

Let's try a more complex query with LEFT JOIN and full-text search:

SELECT name, orders.product FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE MATCH('johnson | white', customers);

This will return:

• Alice Johnson with her 2 orders (Laptop and Tablet)
• Carol White with her 1 order (Monitor)

You can combine full-text search with attribute filtering:

SELECT name, orders.product, orders.quantity FROM customers INNER JOIN orders ON orders.customer_id = customers.id WHERE MATCH('alice | bob', customers) AND orders.quantity > 1;

This returns only Bob's Phone order with quantity=2, since Alice's orders have quantity=1.

MATCH() Without Table Name

You can also use MATCH() without specifying a table name. In this case, the search is applied to the left (main) table:

SELECT name, orders.product FROM customers INNER JOIN orders ON orders.customer_id = customers.id WHERE MATCH('smith');

This is equivalent to MATCH('smith', customers) - it searches for "smith" in the left table (customers), returning both Bob Smith's and John Smith's results. However, since we're using INNER JOIN, only Bob Smith appears (as John has no orders).

Let's try with LEFT JOIN:

SELECT name, orders.product FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE MATCH('smith');

This returns both Bob Smith with his Phone order and John Smith with NULL order values.

You can combine MATCH() without table name with MATCH() that specifies a table:

SELECT name, orders.product FROM customers INNER JOIN orders ON orders.customer_id = customers.id WHERE MATCH('smith') AND MATCH('phone', orders);

This searches for "smith" in customers (left table) and "phone" in orders (right table), returning only Bob Smith's Phone order.

Remember the important rules for JOINs:

1. When selecting fields from two tables, prefix fields from the right table (e.g., orders.product)
2. Use MATCH() with table name to specify which table to search: MATCH('term', table_name)
3. MATCH() without table name applies to the left (main) table
4. INNER JOIN requires matches in both tables
5. LEFT JOIN includes all rows from the left table, even without matches