JOIN clause

The JOIN clause is used to combine rows from the database table and the model table on a related column. The basic syntax for joining from the data table and model is:

SELECT t.column_name1, t.column_name2, FROM integration_name.table AS t 
                                       JOIN mindsdb.predictor_name AS p WHERE t.column_name IN (value1, value2, ...);

JOIN example

The following SQL statement joins the home_rentals data with the home_rentals_model predicted price:

SELECT * FROM db_integration.house_rentals_data AS t JOIN mindsdb.home_rentals_model AS tb 
                                                       WHERE t.neighborhood in ('downtown', 'south_side');

number_of_rooms	number_of_bathrooms	sqft	location	days_on_market	initial_price	neighborhood	rental_price	select_data_query	external_datasource	when_data	rental_price_original	rental_price_confidence	rental_price_explain	rental_price_anomaly	rental_price_min	rental_price_max
0	1	484	great	10	2271	south_side	2271	nan	nan	nan	nan	0.99	{"predicted_value": 2243, "confidence": 0.99, "confidence_lower_bound": 2200, "confidence_upper_bound": 2286, "anomaly": null, "truth": 2271}	nan	2200	2286
1	1	674	good	1	2167	downtown	2167	nan	nan	nan	nan	0.99	{"predicted_value": 2197, "confidence": 0.99, "confidence_lower_bound": 2154, "confidence_upper_bound": 2240, "anomaly": null, "truth": 2167}	nan	2154	2240
0	1	529	great	3	2431	south_side	2431	nan	nan	nan	nan	0.99	{"predicted_value": 2432, "confidence": 0.99, "confidence_lower_bound": 2389, "confidence_upper_bound": 2475, "anomaly": null, "truth": 2431}	nan	2389	2475
3	2	1219	great	3	5510	south_side	5510	nan	nan	nan	nan	0.99	{"predicted_value": 5550, "confidence": 0.99, "confidence_lower_bound": 5507, "confidence_upper_bound": 5593, "anomaly": null, "truth": 5510}	nan	5507	5593
1	1	398	great	11	2272	south_side	2272	nan	nan	nan	nan	0.99	{"predicted_value": 2252, "confidence": 0.99, "confidence_lower_bound": 2209, "confidence_upper_bound": 2295, "anomaly": null, "truth": 2272}	nan	2209	2295